asked on
Query that shows all differences between two tables.
After I import my new bill of material part list into access. I need a query that compare that entire table with the entire existing table and create a table with the differences. Some parts could have been deleted, some added, and some the quantities could have changed, different rev levels, weight change, etc., then I need it to create a table with the differences. I've managed to create two queries to create two separate tables, a parts added and a parts deleted table. This, however, isn't exactly what i need.
Also, after all of that happens, I need to update the existing table with all of the changes. Can anyone suggest the easiest way to accomplish this?
Also, after all of that happens, I need to update the existing table with all of the changes. Can anyone suggest the easiest way to accomplish this?
Microsoft AccessMicrosoft 365
Last Comment
Smilesxl
ASKER
Actually there is additional columns on the the table that are used for data entry, however I suppose I could separate that data out onto a separate table and link the part number. Not an issue, however, I was hoping to not have to write 20 queries to compare the other 20 or so columns of actual data, there has to be an easier way.
You don't need 20 queries but you do need the three I described.
I would separate out the data you maintain from the data you import. It is much cleaner to simply replace the table. I would only do the matching logic if I actually needed to know what changed.
I would separate out the data you maintain from the data you import. It is much cleaner to simply replace the table. I would only do the matching logic if I actually needed to know what changed.
ASKER
I need to know what changed.
ASKER
I guess I'm not understanding the inner join.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
hi,
if you want to have a singular process, showing all changes, you should do this by VBA generating a change report table, where all changes are a single records like eg:
part 4711 new record
part 4712 field 3 changed - old value: ..... - new value: .....
part 4712 field 7 changed - old value: ..... - new value: .....
part 4712 field 23 changed - old value: ..... - new value: .....
part 4713 missing record
...
...
...
is that, what you want to achieve ?
if you want to have a singular process, showing all changes, you should do this by VBA generating a change report table, where all changes are a single records like eg:
part 4711 new record
part 4712 field 3 changed - old value: ..... - new value: .....
part 4712 field 7 changed - old value: ..... - new value: .....
part 4712 field 23 changed - old value: ..... - new value: .....
part 4713 missing record
...
...
...
is that, what you want to achieve ?
Here's a picture of the form used to display the differences. In our case, we have to fix them manually since someone has to research them to determine which is correct. I hid most of the names. But you can see a spot of blue text. Clicking on that will bring the user to the standard maintenance form where he can change the data as needed. The report is similar.
To get the green highlight, you need to use conditional formatting for each field. So, to be consistent, I always highlight the Access side of the compare when it is different from FoxPro.
![Differences]()
To get the green highlight, you need to use conditional formatting for each field. So, to be consistent, I always highlight the Access side of the compare when it is different from FoxPro.
ASKER
Thanks.
To actually do the compares, you will need three different queries.
A left join to find adds
A Right join to find deletes
An Inner Join where you compare column by column to identify field differences.