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.
> Hi,
>
[quoted text clipped - 21 lines]
> Your help would be much appreciated.
> Thanks