Unmatched/Duplicates Query

marsh · December 12th, 2009, 08:16 PM

I have three tables, identical specs for the fields. There are more tables, which are joined. I need to compare them, find all the duplicate records so as to delete the duplicates so that no records in tblA exists in tblB, or tblC. After I have assured myself that all records are unique, I will then append the records in B into A and C into A.
What would be the best approach to identify and delete any duplicates.
Thanks,
Marsh

P2P4Fam6 · December 13th, 2009, 02:37 PM

1. When you say Tables A, B, and C contain identical specs for the fields--are you implying that the field names in ALL three tables are named exactly the same and the field names in ALL three tables are in the same order when viewed in the design view for each table?
2. Do you have the same primary key set for ALL three tables. e.g. The SSAN field name is the same primary key for ALL three tables.
3. When you say you want to find duplicate records--are you implying that you are looking for duplicate records which have the same exact data in ALL fields when compared to another record in a different table? Or, are you simply looking for duplicate records based on the primary key? In my example above, using a SSAN field--my personal record would be a duplicate record if my Social Security Number (SSAN primary key) is found in at least 2 of the three Tables (A, B, or C).
Thanks!

marsh · December 13th, 2009, 06:50 PM

1. The users who created the data in these tables were given a copy of a pre-existing table(structure only) so everything about them is identical.
2. The Primary Key field has not yet been set, but will be when we are sure there ae no dupes. Another table has what will be perfect foreign key fields. Primary key and join will be created when consolidation of these three tables is done. Duplicates in our situation would have identical data
3. Duplicates in our situation would have identical data

zeronexxx · December 13th, 2009, 10:32 PM

i am not sure but worth trying.

PHP Code:


			

SELECT * FROM A
INNER JOIN B ON B.FIELD1=A.FIELD1 AND B.FIELD2=A.FIELD2
INNER JOIN C ON C.FIELD1=A.FIELD1 AND C.FIELD2=A.FIELD2

AND

SELECT * FROM B
INNER JOIN B ON B.FIELD1=C.FIELD1 AND B.FIELD2=C.FIELD2

P2P4Fam6 · December 16th, 2009, 01:01 AM

Here you go:
Please back up or make a full copy (recommended) of your database before you perform this solution:
1. Append ALL records from Table B to Table A using the following steps:
a. Select Table B without opening the table
b. Press Ctrl-C for Copy and then press Ctrl-V for Paste
c. In the Paste Table As dialog box, change the Table Name to the exact name (case-sensitive) of Table A
d. Change the Paste Options to: Append Data to Existing Table
e. Click OK (Append action complete)
2. Append ALL records from Table C to Table A using the following steps:
a. Select Table C without opening the table
b. Press Ctrl-C for Copy and then press Ctrl-V for Paste
c. In the Paste Table As dialog box, change the Table Name to the exact name (case-sensitive) of Table A
d. Change the Paste Options to: Append Data to Existing Table
e. Click OK (Append action complete)
3. ALL records should now be in Table A.
4. Make a copy of Table A (Structure Only) and name as MainRecords. This empty table (MainRecords) will be used to paste ALL non-duplicate records during the last step which is ultimately the end-result of what you are trying to achieve
5. Go to your Query Wizard and select Simple Query Wizard.
a. Select Table A and drag ALL Available Fields to Selected Fields box.
b. Click Next and select the Modify the query design radio button
c. Click Finish
6. In your query design view, make the following changes:
a. Click Design on top of the window toolbar and click the Sum Totals button (looks like an oversized sideways M).
1) This action adds the Total row to the bottom of your query screen and;
2) populates the words Group By in the Total row for each selected field
b. Close the query and save the query as the default name
7. Run the query
a. You will now see ALL records in your query with the exception of duplicate records. In other words:
1) Query will not show any record that is an exact duplicate (ALL fields containing the exact data)
2) E.g.: If Table A contains 3 duplicate records, then query will only select 1 of the 3 records and so on.
b. Copy ALL records by highlighting ALL record rows and press Ctrl-C for Copy.
8. Close query
9. Open table MainRecords which should be empty at this point.
a. Final step: Highlight first record row in table MainRecords and press Ctrl-V for Paste

You now have ALL records in the MainRecords table that do not contain duplicate records.
Cheers,