Delete Dup records

[micahs_10]

Hello. I have 2 tables with identical fields. The first table contains about 200 records, and the second table has 40 records. Of the 40 records, 20 of them contain 4 fields which match records in the first table.

I'm trying to create a Delete Query which will delete the entire record from table 1 for each of the 20 dups. So basically I'm trying to say that if Field 1, Field 2, Field 3, and Field 4 for a record in table 2 matches the same 4 Fields in Table 1, delete the entire record from table 1.

Ok, that wasn't so "basic", but I hope I explained it well enough. Does any one know how to do this?

Thanks!

 

[SydneyGeek]

First thing, build a query based on each table that concatenates the 4 fields you want to match --
Key:[Field1] & " " & [Field2] & " " & [Field3] & " " & [Field4]. You should also have whatever identifying fields you need, as separate fields in the query. For the first table, call the query qryMatch1. For the second table, call the query qryMatch2.
Next, build a third query that pulls them together --
New query, Design view, add qryMatch1 and qryMatch2 to the display. If any relationships connect the tables, deleted them for this query. Replace with your key match: drag Key from the first query onto Key for the second query. Double-click the asterisk in qryMatch1. Take a look at the query result -- you should see ONLY records in Table 1 that have a match in Table 2.
If this is a once-off, you can highlight all the records in the left margin of the datasheet, and delete them. IF you may need to do this again, go back to Design view. In the Query menu, select Delete. Save the query, giving it a name (eg qryDeleteDuplicates). To clear duplicate records, double-click the query icon.

Denis

 

[buckeye_td]

You can skip the extra query builds and save yourself some time. Just create a query with both of the tables with joins between field 1 of each table, between field 2 of each table, field 3 of each table and field 4 of each table. Take a look at the results of the query and make sure they are the records that you expected. Go back to design mode and change the Query Type to Delete. Run it. If you get an error message that says 'Could not delete from specified tables' go back to design mode and click properties for the query and make sure that the Unique Records value is set to Yes.

TD

 

[micahs_10]

Thank you both for your reply. I first tried TD's method because the fewer steps the better…I'm an Access novice. But when I tried to run the Delete Query I got a message that said "You did not specify the table to delete from" (that's probably something very simple to correct, but I wasn't sure). Also, when I checked the query's properties, I didn't see anything about Unique Records.

So I tried Denis' method and got the result I was looking for. Thanks again to both of you!

 

[buckeye_td]

FYI, the reason it didn't work is because there was nothing selected. I left out the "Double-click the asterisk in the first table" part of the process which is what would determine the 'FROM' for the delete query.

TD