Delete records from table based on Inner Join query - Please help

[metad]

Hi. I have used a lot of times and searched this forum to fin a solution for following problem without luck:

I want to delete duplicate records from a table (Table1). I have made a query as following:

Query name: DeleteEntries

SELECT Query1.Field1, Query1.Field2
FROM Query1 INNER JOIN Query2 ON Query1.Field1 = Query2.Field1
WHERE (((Query1.Field2)="test"));

And I want to delete records in table 1 based on this query’s records from VBA.

I have made a DLookup code, but I get only one and one record and can not delete all based on DLookup:

Recs = Nz(DLookup("Field1", " DeleteEntries"))

SQLDel1 = "DELETE Table1.Field1 FROM Table1 WHERE (Table1.Field1 = """ & recs & """)"
DoCmd.RunSQL SQLDel1

Can someone help me please?

Thanks in advance.

 

[FoFa]

DELETE FROM Table1 WHERE Table1.PK in
(SELECT MAX(Query1.PK)
FROM Query1 INNER JOIN Query2 ON Query1.Field1 = Query2.Field1 AND Query1.Field2 = Query2.Field2
WHERE (((Query1.Field2)="test")) Group BY Query1.Field1, Query1.Field2);

This should select the max primary key (PK) from Query1 for each duplicate, then delete that row. You could use MIN instead of MAX, but if they are duplicates, it should not matter.

 

[metad]

It does not work!

Thank you for your answer, but the code dose not work. I do not use PK for the table, because this is an update table based on a link table.

I hope you can help me more...

Thank you

 

[FoFa]

You do not have a primary key defined on your table?
Well that sure adds a complexity to the problem. How do you determine you have a duplicate in that case?

 

[metad]

Duplicated records

I have a table (Table1) which contains Fields as F1, F2 etc. This table is an update Table based on a link table. I could make a PK on this table, but that does not help me, because the PK for me is [F2] & " " & [F1]. Therefore I make a query based on this table and make a new field called "Flights" ([F2] & " " & [F1]). I do the same with the link table (make a query and field"Flights").

Users can add values to the Table1 even that the values are not in table2 (link table). These values can later on be added to the link table (Web-based tabel) and I will therefore move the user-defined values after that the same values have been added to the link table.

In a Select query Inner Join I can find out if the link table and table1 have duplicated records, and I will delete these records from the table1.

I hope I have explained it clearly.

Thanks agian.

 

[FoFa]

Well once you define the duplicates, since you do not have a unique PK, how do you determine which to delete? Your PK maybe [F2] & " " & [F1], but it is not unique (so it really is not a primary key). So, SELECT F2, F1, Count(*) from Table1 group by F2, F1 having count(*) > 1 will give you a list of the duplicates. But how do you decide which of the duplicates to delete?