Delete Query Help 2

[sjck]

I need to delete records from one the tblworkingtable. I am able to select the records to be deleted --however everytime I try to put it in a delete query I receive an error (e.g. not an updateable query) I have attached my select query that works.. but I am at a loss of where to go from here... Any help would be greatly appreciated

SELECT tblWorkingTable.ID, tblWorkingTable.CaseMatch, tblWorkingTable.[PDF Match], tblWorkingTable.Participating
FROM tblWorkingTable INNER JOIN [Records to Remove] ON (tblWorkingTable.[PDF Match] = [Records to Remove].[PDF Match]) AND (tblWorkingTable.CaseMatch = [Records to Remove].CaseMatch) AND (tblWorkingTable.ID = [Records to Remove].ID) AND (tblWorkingTable.Participating = [Records to Remove].Participating)
WHERE (((tblWorkingTable.CaseMatch)=[Records to remove].[pdf match]) AND ((tblWorkingTable.[PDF Match])=90 Or (tblWorkingTable.[PDF Match])=0)) OR (((tblWorkingTable.CaseMatch)=[Records to remove].[pdf match]) AND ((tblWorkingTable.Participating)="N/A"));

 

[AlexCuse]

This is just a shot in the dark, but have you tried adding DELETE DISTINCTROW? I find I need to do this when using joins in a delete query at times. Not sure why though.

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

What are the PrimaryKeys of tblWorkingTable and [Records to remove] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

I received the table with out a primary key, hence the duplicates. The primary key should hve been ID.

 

[PHV]

The simplest way to remove duplicates is to copy the structure of the table, create a unique composite index and then append the data ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

PHV -- How would I choose the record to keep. The problem with this data is that instead of updating the record they added a new record. My select query selects the records I would like to delete.

 

[gwinn7]

Good idea on the DELETE DISTINCTROW! I will have to try that!

Anyhow, when I come up with problems like this, I use the IN clause. Its very easy. I hope it applies to your situation...

Example...

DELETE Table1.ID
FROM Table1
WHERE (((Table1.ID) In
(SELECT ID FROM Table1 WHERE title = 50)));

You can use your SELECT query as the source of which records you want deleted. Then, run a DELETE on it.

This isn't a very fast technique, but it works. The IN clause can be VERY VERY slow, so I don't recommend using it unless you have to.

Gary
gwinn7

 

[sjck]

Thanks - I will give it a try - At this point I don't care how slow it is - just as long as it works.. Thanks again