Differences between tables (specifics)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I just realized that my problem won't get much attention unless I post specifics. Here they are

I have one file (call it MONDAY) with the following fields
SEC_ID, SEC_DESC, SEC-SYMBOL, SEC_CLIENTS, CUSIP_NUMBER, ORIGINAL_PAYMENT_TYPE, RECORD_DAT

Then it gets replaced with another file (call it TUESDAY) which is cumulative - meaning it includes all the records from the MONDAY file but some things change. It adds records and some of the previous records are modified

I need to be able to take MONDAY and compare it to TUESDAY, revealing eveything that has added or changed

But wait, there's more! Some of the records have blank data in some of the fields. This seems to screw up the Find Duplicates Query I was trying to use. I was trying to append one table to the other and then run a Find Duplicates. The idea was that I could then delete the duplicates and only the different records would remain. However, the Find Duplicates Query seems to have a hard time with blank field values

Any suggestions as to how I can accomplish this seemingly simple task

thanks!!!
- S
 
Stefan Johnson said:
I just realized that my problem won't get much attention unless I post specifics. Here they are:

I have one file (call it MONDAY) with the following fields:
SEC_ID, SEC_DESC, SEC-SYMBOL, SEC_CLIENTS, CUSIP_NUMBER,
Click to expand...
ORIGINAL_PAYMENT_TYPE, RECORD_DATE
Then it gets replaced with another file (call it TUESDAY) which is
Click to expand...
cumulative - meaning it includes all the records from the MONDAY file but
some things change. It adds records and some of the previous records are
modified.
I need to be able to take MONDAY and compare it to TUESDAY, revealing
Click to expand...
eveything that has added or changed.

I needed a quick and dirty way to do something similar. I wanted to monitor
our inventory database to see if anyone had made changes to prices,
descriptions, etc. or if any new records had been added. I kept an archived
copy of our inventory file and compared it to the current file using the
following query design. Be forewarned, I am a Access novice with zero formal
training. The solution that I came up with may not be very elegant but it
does what I needed. If any of the pro's here have a better way to accomplish
what I am doing or can clean this up I am all ears.

I did an example using your tables and only the following fields:

SEC_ID, SEC_DESC, SEC-SYMBOL, SEC_CLIENTS, ORIGINAL_PAYMENT_TYPE

Assuming that SEC_ID is the key field it will find new records for TUESDAY
and list them first with a "Y" in the "NEW" field and then list any records
that have had changes made to them. It will only populate the fields that
have been changed but it will show the old value and the new value in the
format:

OLD VALUE > NEWVALUE

Records without any changes are not selected. Selected records will have
unchanged fields left empty. Null fields will show as a "Blank" It will not
find records that exist in MONDAY but have been deleted in TUESDAY but you
could do a seperate query for that.

SELECT IIf([MONDAY]![SEC_ID] Is Null,"Y",Null) AS NEW, TUESDAY.SEC_ID,
IIf(Nz([MONDAY]![SEC_DESC],"Blank")<>Nz([TUESDAY]![SEC_DESC],"Null"),Nz([MON
DAY]![SEC_DESC],"Blank") & " > " & Nz([TUESDAY]![SEC_DESC],"Blank"),Null) AS
SEC_DESC,
IIf(Nz([MONDAY]![SEC_SYMBOL],"Blank")<>Nz([TUESDAY]![SEC_SYMBOL],"Blank"),Nz
([MONDAY]![SEC_SYMBOL],"Blank") & " > " &
Nz([TUESDAY]![SEC_SYMBOL],"Blank"),Null) AS SEC_SYMBOL,
IIf(Nz([MONDAY]![SEC_CLIENTS],"Blank")<>Nz([TUESDAY]![SEC_CLIENTS],"Blank"),
Nz([MONDAY]![SEC_CLIENTS],"Blank") & " > " &
Nz([TUESDAY]![SEC_CLIENTS],"Blank"),Null) AS SEC_CLIENTS,
IIf(Nz([MONDAY]![ORIGINAL_PAYMENT_TYPE],"Blank")<>Nz([TUESDAY]![ORIGINAL_PAY
MENT_TYPE],"Blank"),Nz([MONDAY]![ORIGINAL_PAYMENT_TYPE],"Blank") & " > " &
Nz([TUESDAY]![ORIGINAL_PAYMENT_TYPE],"Blank"),Null) AS ORIGINAL_PAYMENT_TYPE
FROM MONDAY RIGHT JOIN TUESDAY ON MONDAY.SEC_ID = TUESDAY.SEC_ID
WHERE (((MONDAY.SEC_ID) Is Null)) OR
(((Nz([TUESDAY]![SEC_DESC],"Blank"))<>Nz([MONDAY]![SEC_DESC],"Blank"))) OR
(((Nz([TUESDAY]![SEC_SYMBOL],"Blank"))<>Nz([MONDAY]![SEC_SYMBOL],"Blank")))
OR
(((Nz([TUESDAY]![SEC_CLIENTS],"Blank"))<>Nz([MONDAY]![SEC_CLIENTS],"Blank"))
) OR
(((Nz([TUESDAY]![ORIGINAL_PAYMENT_TYPE],"Blank"))<>Nz([MONDAY]![ORIGINAL_PAY
MENT_TYPE],"Blank")))
ORDER BY IIf([MONDAY]![SEC_ID] Is Null,"Y",Null) DESC , TUESDAY.SEC_ID;
 
Dude.. this is incredible. The real world solution ended up being removing the multiple match criteria and only focusing on SEC_ID, which worked fine for what it is needed for.

However, I am going to save this query and print it out so perhaps I can provide a more elegant solution in a bit

Thanks again!!!
- S
 
Back
Top