VBA to run compare query and highlight the mismatches in report (1 Viewer)

penchalas

Registered User.
Local time
Tomorrow, 02:15
Joined
May 14, 2019
Messages
26
Hi,


I have a form which imports the excel spreadsheets as tables and display the mismatches in a data sheet view (Button control in form calls the comparison query)


Now my requirement is:
1) To capture the data difference in a report by calling the report from button control in a form (which runs the comparison query). Basically once the user clicks the button, report should be opened with query result



2) Highlight the cells with different color having the mismatch in the report
 

penchalas

Registered User.
Local time
Tomorrow, 02:15
Joined
May 14, 2019
Messages
26
Sorry I didn't get you.
How to do that? i need the form to be main interface from where reports or datasheets need to be displayed
 

isladogs

MVP / VIP
Local time
Today, 20:45
Joined
Jan 14, 2017
Messages
18,186
Use the same query that shows the mismatched records as the record source for your report. Run the report using a button on the form that shows the datasheet view.

Use conditional formatting to highlight differences in data on the report.
 

penchalas

Registered User.
Local time
Tomorrow, 02:15
Joined
May 14, 2019
Messages
26
Ok.


On conditional formatting in the report:
Am using union all query to get the mismatched record. Even though there is a difference in one column, entire row will be fetched .


Ex: One such mismatch is shown below


Source data:



SYSTEM,Policynumber, State, Premium
Legacy,1234A123 , Indiana, $4000


Target data:
SYSTEM,Policynumber, State, Premium
ABC Ins,1234A123 , Indiana, $4300


The output of the query would be :
SYSTEM,Policynumber, State, Premium
Legacy,1234A123 , Indiana, $4000
ABC Ins,1234A123 , Indiana, $4300


Now I need $4000 should be highlighted in some color in the report. How to achieve that
 

isladogs

MVP / VIP
Local time
Today, 20:45
Joined
Jan 14, 2017
Messages
18,186
Do you really need to use a union all query.
Why not use an unmatched query joining both tables by a unique field (e.g policy number?) and just show the items where one of more fields aren't the same

The easiest way to understand conditional formatting is to look at the Access help files. In your case using two different records you would need a different approach to that with the unmatched query
 

penchalas

Registered User.
Local time
Tomorrow, 02:15
Joined
May 14, 2019
Messages
26
Actually we don't have unique key in the table. And that is the reason, we use union all query.
Could you please suggest some approach for this (through VBA or access in built options)
 

isladogs

MVP / VIP
Local time
Today, 20:45
Joined
Jan 14, 2017
Messages
18,186
Can you upload a stripped down copy of your database with those two tables, the query, form and report and anything else relevant to this question.
Include enough data to illustrate the issue you are talking a out.
 

penchalas

Registered User.
Local time
Tomorrow, 02:15
Joined
May 14, 2019
Messages
26
Am uploading my access database
CompareData button gives me the result of SRCData and TGTData tables in a datasheet view
I have used union all query to compare as there is no primary key in tables

Now i need a report from form, where the actual difference are highlighted in some color

Just to let u know, in the tables, the comparison is based on policy number and state
 

Attachments

  • San123.accdb
    428 KB · Views: 56

isladogs

MVP / VIP
Local time
Today, 20:45
Joined
Jan 14, 2017
Messages
18,186
Answering on my tablet so I can only do limited changes to your design.
Several things

1. All tables should have a primary key field. I've added an autonumber ID field to each.
2. Using a union query means you have two records for each difference. There is no need for this.
I've created two queries to compare the data with each difference in the same record.
One query joins the tables by policy number and State. The other by ID.
These give the same results with the data supplied. Use whichever you prefer.
3. I've done a very simple report based on the first query. I've added some conditional formatting to one field to show one possibility. Adapt as you wish

However, presumably the idea is to synchronise the data so you can just have one table. To do so, use an update query. I have an extended article on this topic on my website that you might find useful http://www.mendipdatasystems.co.uk/synchronise-data-1/4594514001

Hope that helps you move forward
 

Attachments

  • San123_v2.accdb
    556 KB · Views: 69
Last edited:

penchalas

Registered User.
Local time
Tomorrow, 02:15
Joined
May 14, 2019
Messages
26
Thanks Much!
But i have a question. What if we have a text field instead of premium?
How to capture that difference?


Ex: Instead of premium, i have City field. And in source we have address as "ABC", and in target we have "XYZ". In this case, how to show the difference in the report?
 

isladogs

MVP / VIP
Local time
Today, 20:45
Joined
Jan 14, 2017
Messages
18,186
Use the same approach.
In the query filter criteria, enter something like SrcData.City<> TgtData.City.
If more than one field could be different, enter the filter criteria on a new line.
Adapt the report accordingly. Obviously the Difference column couldn't be used
 

Users who are viewing this thread

Top Bottom