compare data between two sets

[emcf]

morning/afternoon/evening all,

its been a while but i now need to ask another question -

i get a list (in excel) sent to me every day. at the moment i need to go thru it manually to see which items are new, which are no longer there and which items have had amendments made to them....this is mind numbingly boring as you can probably imagine so what i want to do is have an access database that imports today's excel file and compares it to yesterday's list.

importing the excel sheet is something i can do but what i am struggling to do is to think of a way of comparing the two different tables.........

any suggestions most welcome (new boss to impress!!)

thanks in advance...

 

[ColinEssex]

How about the "Find unmatched query wizard"? to compare the 2 tables.

Col

 

[emcf]

thanks col,

this find unmatched doesn't seem to be able to spit out the data i want spat out. i reckon i'll have to try concatenating each record and then compare the two recordsets...that should highlight any alterations.

the find unmatched should help in finding deletions/additions.

 

[The_Doc_Man]

I have a solution that I could outline for you, but it requires lots of code. Before I even bother, are you comfortable with VBA?

I have another solution but it might just be tedious, requiring three queries and an overarching UNION query.

Import your new spreadsheet into a temporary table with a fixed name. Erase this table when you are done with it so you can make room for the next day's work.

Now, have three queries prepared. Call the main table M and the imported spreadsheet table T.

Query 1: Find all records in M for which a corresponding record exists in T as an INNER JOIN. (Presumably, you have an available key for this.) Perhaps you would use a simple JOIN between M and T on the key field. If this won't blow the limit on the number of fields in a query, include the corresponding fields from both tables.

Query 2: Find all records in M using the same criteria as Query 1 BUT make it the OUTER JOIN and reject any records that filled in the fields from T(i.e. matched). Include all the fields from M and T, knowing full well that you will get nulls for records that have values from M but no T counterpart. (To prevent problems, you might just go ahead and use the NZ function to supply an empty string or a 0 as appropriate to the fields from M.)

Query 3: Find all records in T using the same criteria as Query 1 BUT make it the OUTER JOIN and reject any record that filled in the fields (i.e. matched). Include all the fields from T and M, knowing full well that you will get nulls for records in T that have no M counterpart. Again, you might wish to protect yourself by using the NZ function.

Now the overarching UNION query should combine all three of the individual queries and supply an extra constant field for each query. Show "MATCHED" for the records from Query 1. Show "NEW" for the records from Query 3. Show "LOST" for the records from Query 2. Add one more field that is a comparison of the fields in the record. This field will be either TRUE or FALSE based on whether all fields in the UNION query's record match.

Take it from there. You can select all records that don't match in the UNION query. What you do with them is your call.

 

[emcf]

thanks folks,

i've bodged together a solution of sorts using a few layers of find unmatched queries....although it is a bit clunky and slow....i'll give doc_man's suggestion a try...although hold off on the VBA code solution...i'm not too hot at that!