Is it possible to write a query that will compare two tables in Access 2013?

Is it possible to write a query that will compare two tables in Access 2013?

We had a situation where there were two copies of a database and different users were updating the wrong one

So now I've been asked to compare and merge table together

tables are now in the same database
Ian PriceIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
just make a left join between the new table and the old and the records that are not matched are to merge...repeat the opposite if someone used the old table
Ian PriceIT ManagerAuthor Commented:
Sounds good but my knowledge is really low on Access could you explain abit more or point me the direction of a link that will explain

There are three situations you need to handle and only two of them can be handled automatically.
1. Rec in Master table but not in Duplicate table
2. Rec in Duplicate table but not in Master table
3. Rec in both but different

For #1, the record was deleted in the duplicate database so delete it from the master (this is assuming that deletes are even allowed)
For #2. The record was added in the duplicate database and so must be added to the master.
For #3.  Someone needs to examine EACH of these records MANUALLY and decide which value is accurate.  You have no way of knowing which fields were modified when.  If the Master was changed after the duplicate, you don't want to clobber those changes but if the duplicate has the most recent change then those values should prevail.  It is also possible that you would need to take some values from each record.  If you had the presence of mind to add changeby and changedate fields to every table when you designed the app, those will help considerably to sort out how to handle the difference records.

How bad the situation actually is depends on how long the duplicate was in use and whether you have any way of validating what the correct values should be.

For #1, you would use a Left Join with criteria that looks for null in the duplicate table
Select * from Master Left Join Duplicate on Master.PK = Duplicate.PK where Duplicate.PK Is null
For #2, you would use a Right join
Select * From Master Right Join Duplicate on Master.PK = Duplicate.PK where Master.PK is null
For #3, you use an Inner join and a lengthy Where clause that compares each non-key field.  Don't forget to handle nulls because where something <> null will not return true so any field that started out null and became something or started out as something but became null would not be selected as having changed unless you specifically check for null.  A simple but tedious technique concatenates a ZLS to each field

Where Master.fld1 & "" <> Duplicate.fld1 & "" OR Master.fld2 & "" <> Duplicate.fld2 & "" OR .....  Of course, if a field cannot be null, you don't have to use the concatenate ZLS trick.

If you have the good fortune to have changedate fields in all tables, post back and I'll suggest how to figure out which updates you can handle automatically and which still need to be handled manually.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian PriceIT ManagerAuthor Commented:
That all went over my head apologies

Maybe I can break it down

Table one has 6486 records
Table two has 6669 records

Can I run a query to see if there are any duplicate in the two tables? In theory Table two should have 6484 duplicates

Is this possible?

Please read my explanation again.  Fixing this goes well beyond record counts.

Start by creating the outer joins.  Use the QBE.  Add the Master table and then add the duplicate.  Draw a join line between the two on the PK.  Double click on the middle of the join line.  Choose the option that says to return all the "Master" rows.  Then add criteria on the "Duplicate" PK to select only rows with null PK's.  This will return all the rows in the Master that are not in the Duplicate.

To create the reverse query, start the same way except that when you double click the join line, choose the option that returns all the "Duplicate" records.  This time the criteria will be where the PK of the Master table is null.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.