How Can I compare information in Access?

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

I have to two huge Excel files about 6,000 rows each with about 8 fields,
which should have identical information. What I need to do is compare both
files to make sure that all fields have identical data and if it doesn’t then
I need to see those differences. In addition if there are records which are
in one file but not the other I also need to see this. If there’s an easy way
of doing a comparison like the one I explained above I would really
appreciate the feedback.
 
Link to both Excel files (6,000 is not huge) as linked tables.
There are query wizards you can use to build both find duplicates and find
unmatched queries you can use to return the results you want.
 
Great, but how is a duplicate query going to help me find differences between
records? I’m trying to find differences between related records that’s my
main concern. Can you let me know the steps to accomplish this? Thank you in
advance for all your help.
 
I'm thinking the following will work:
Use Access to link to both tables, call them A and B.
Create a query that includes all fields from from Table A and Table B. Add A
first, so it will be on the "left".
Create left outer joins between every field that should match in both tables
(i.e., if there are 8 fields, you will have 8 join lines running between the
tables). This will cause every record of Table A to be listed. This way, if
the fields in any record in A does not match every field in the comparative
record in B, then the A record will still get listed. You change the join
type by double clicking a join line, to get the join properties dialog box.
For your criteria, you put Is Null into any field under table B; this will
cause the query to ignore all records that are exact matches.

The above will get one "side" of your comparison. To get the other "side",
create another query where table B is fully listed, with the IsNull under any
field in table A.

If you want this all in one big list, you could create a union query
utilizing the SQL code of both queries. This has done to be done in SQL view.
 
Back
Top