Duplicate Values Query

  • Thread starter Thread starter David Patterson
  • Start date Start date
D

David Patterson

I have two tables each with 4 columns:

Table 1:
Unique ID
Previous Team
Previous Business Unit
Previous IT Unit

Table 2:
Unique ID
Present Team
Present Business Unit
Present IT Unit

I want to put the two together in a table

Previous Team Previous BU Previous IT Unique ID Present Team Present BU
Present IT

That bit's OK.

Some records will not have changed at all but some will have, say, changed
the IT Unit No. I want to be able to identify where there are differences
between the respective columns, ie. all records where the IT number does not
agree with the present one, or all records where the Previous Team and
Previous BU do not agree with the present one. Any ideas that are
reasonably straightforward, please?

Many thanks.

David
 
Forgot to say that I am using Access 2000 although I may have to convert to
Access 97 to share files at work.
 
Well, does UNIQUE ID stay constant BETWEEN the tables? If so,

SELECT T1.*, T2.*
FROM Table1 as T1 INNER JOIN Table2 as T2
ON T1.[Unique id] = T2.[Unique id]
WHERE T1.[Previous Team] <> t2.[Present Team]
OR T1.[Previous Business Unit] <> T2.[Present Business Unit]
OR T1.[Previous IT Unit] <> T2.[Present IT Unit]

If you are using the query grid.
Add both tables to the query
Join them on the Unique ID field

Under each of the Table 1 fields in the criteria section enter
<> Table2.[NameOfTheCorrespondingField]
BUT stair step these criteria one to each of three criteria lines.


If none of the items stays constant then you have a rather complex and tedious exercise.
 
Back
Top