Append NOMATCH DATA

  • Thread starter Thread starter learning_codes
  • Start date Start date
L

learning_codes

Hi,

I use "Find Unmatched Query Wizard" from MS Access 2002 Wizard and give
me only one choice to pick "matching field" between two tables. I have
12 tables that have 20 columns. I'm looking for your help to give me
an example of looking all 12 tables if there is any changes from any 20
columns. If there is a change and append the record to another "Table
- Change Data" table and record the new field where the column changes
from TABLE1 to Table 5.

Table 1
Table 2
Table 3
Table 4
Table 5
Table 6
Table 7
.......

Table - Change Data (appends from any of Table 1 to 12
copy all columns (one record) plus new columns that tell where the data
source "from Table 5"

Your help would be much appreciated.
Thanks
 
How do you identify a record in table1 that matches a record in table 2? Is
there one or two fields that can be used to match the records?

Is table 1 a Master Table and the remaining tables (2 to 12) subordinate
tables? Or are you trying to find the differences between all the
combinations of tables?

To find records in T1 that are different than the record in table 2 and
display the data from both tables
SELECT "Table 1 - Table 2 Differences" as [Which Tables], T1.*, T2.*
FROM [Table 1] as T1 INNER JOIN [Table 2] as T2
ON T1.PrimaryKey = T2.PrimaryKey
WHERE
T1.FieldA <> T2.FieldA OR
T1.FieldB <> T2.FieldB OR
T1.FieldC <> T2.FieldC OR
T1.FieldD <> T2.FieldD OR
T1.FieldE <> T2.FieldE OR
T1.FieldF <> T2.FieldF OR
T1.FieldG <> T2.FieldG

Alternatively to just get T1 records that don't exist or are different.
SELECT "Table 1 - Table 2 Differences" as [Which Tables], T1.*
FROM [Table 1] as T1 LEFT JOIN [Table 2] as T2
ON T1.PrimaryKey = T2.PrimaryKey AND
T1.FieldA = T2.FieldA AND
T1.FieldB =T2.FieldB AND
T1.FieldC = T2.FieldC AND
T1.FieldD = T2.FieldD AND
T1.FieldE =T2.FieldE AND
T1.FieldF = T2.FieldF AND
T1.FieldG = T2.FieldG
WHERE T2.PrimaryKey IS NULL

You can run into problems with both the above if the fields contain nulls.
Also, you may run into problems with trying to do 20 fields at once in the
join.
 
Back
Top