Finding duplicate records

[eddie442]

Hello

I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.

Thanks

Dave, England

 

[Liv Manto]

Design query, find duplicates

 

[eddie442]

Thanks but the data is in multiple tables and the find duplicates query only lets you select from one table. I need to browse through all the tables and find how far back clients go. No-one thought to include a date created field in the client table! Any ideas?

Thanks

 

[Liv Manto]

Union SQL all client fields plus the table where they come from:

For example:

Select client_name, '1996 Table' as OrigTable from Table1996
union ALL
Select client_name, '1997 Table' as OrigTable from Table1997
union ALL
Select client_name, '1998 Table' as OrigTable from Table1998
union ALL
Select client_name, '1999 Table' as OrigTable from Table1999

--

Then run a duplicate query on that query...

 

[eddie442]

Thanks very much. Just what I wanted. Cheers for that!