• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 40
  • Last Modified:

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
0
Ian Price
Asked:
Ian Price
  • 2
  • 2
1 Solution
 
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
0
 
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

Ian
0
 
PatHartmanCommented:
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.
0
 
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?

Ian.
0
 
PatHartmanCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now