Comparing Tables

osmethod

Hello,

I have read many articles about comparing tables, like - loops, delete
queries, appending to temp table with index etc

Problem: 2 tables T1 & T2. Data is suppposed to be the same in both
tables but i need to check that this is so. (Maybe a user deleted a
row in one table only or changed a value in a field!). The tables have
no unique index.

Question: How do I compare the tables to resolve the above problem?

Answer: Because (in my particular case) I only have 4 fields in T1 and
T2, I concatenated the the 4 fields into 1 field in both tables and
then "Found unmatched records" between the 2 tables i.e. the 2 tables
represented now as T1a and T2a each with 1 field.

I'd appreciate anyones comments, suggestions or cleverness to resolve
the problem as described and offer a more general solution.

Thank you for your time....

Nov 13 '05 #1

Trevor Best

osmethod wrote:

Hello,

I have read many articles about comparing tables, like - loops, delete
queries, appending to temp table with index etc

Problem: 2 tables T1 & T2. Data is suppposed to be the same in both
tables but i need to check that this is so. (Maybe a user deleted a
row in one table only or changed a value in a field!). The tables have
no unique index.

Question: How do I compare the tables to resolve the above problem?

Answer: Because (in my particular case) I only have 4 fields in T1 and
T2, I concatenated the the 4 fields into 1 field in both tables and
then "Found unmatched records" between the 2 tables i.e. the 2 tables
represented now as T1a and T2a each with 1 field.

I'd appreciate anyones comments, suggestions or cleverness to resolve
the problem as described and offer a more general solution.

Thank you for your time....

Why do you have two tables the same? Is there a point?

Nov 13 '05 #2

Os Method

Hello Trevor,

Yes there is a need....

I get a file on week 1 of each month of test results.
I then get a second file on week 2 of the same month which has the
accumulated results for week 1 and week 2.

The week 1 results in the weeks 2 file could have changed, e.g. a test
value in a field or maybe they omitted a test result which was
originally in weeks 1 file.

Because results are sent to suppliers at the end of each week, I can't
so well send out the accumulative results if week 1 of weeks 2 file is
different than the first weeks 1 file.

I have a results table and a results_temp table.
1st weeks file is OK to import to the results table after some checking.
2nd weeks results file is imported into the "results_temp table",
compared against the "results" table and I then need to show the user
the differences so she can make a decision which is correct.

I hope this explains why...
P.S. Your website has been of great help on the past while.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #3

Trevor Best

osmethod wrote:

Hello,

I have read many articles about comparing tables, like - loops, delete
queries, appending to temp table with index etc

Problem: 2 tables T1 & T2. Data is suppposed to be the same in both
tables but i need to check that this is so. (Maybe a user deleted a
row in one table only or changed a value in a field!). The tables have
no unique index.

Question: How do I compare the tables to resolve the above problem?

Answer: Because (in my particular case) I only have 4 fields in T1 and
T2, I concatenated the the 4 fields into 1 field in both tables and
then "Found unmatched records" between the 2 tables i.e. the 2 tables
represented now as T1a and T2a each with 1 field.

I'd appreciate anyones comments, suggestions or cleverness to resolve
the problem as described and offer a more general solution.

Thank you for your time....

Pretty tough if you have no PK, a find non matched query is a good way
to go about it. What if all fields change for a particular record or
that only one field has changed or looked like it changed but is
supposed to be in fact a different record?

Do the tables come in each week with the same number of records?

Nov 13 '05 #4

osmethod

> Pretty tough if you have no PK, a find non matched query is a good way

to go about it. What if all fields change for a particular record or
that only one field has changed or looked like it changed but is
supposed to be in fact a different record?

Do the tables come in each week with the same number of records?

An Example:
Week 1 File
ID Date Test Result
1 04/08/04 Fat 2.53
1 04/08/04 Pro 1.57
1 05/08/04 Cbh 0.67

Week 2 File
ID Date Test Result
1 04/08/04 Fat 2.53 Same as line 1 above
1 03/08/04 Pro 1.57 Date changed on this one
Line 3 not included at all..!

I concatenated the fields ID,Date,TestResult e.g.
Line 1 above then becomes - 104/08/04Fat2.53

Now if i "Find unmatched" Line1 will not show because it is the same
in both files.

Lines 2 form both tables will show as the date was modified.
Line 3 on 1st table will show because its equivalent is missing in 2nd
table.

I then un-parse the lines and show them to the user. She has to decide
what to do. I use the transaction methods because its an all or
nothing untill a decision is made, which, is right.

Nov 13 '05 #5