Delete Query question

[jpatchak]

The following select query gives me the set of records I want to delete:

SELECT tblCaseNumbers.*
FROM (tblCaseNumberDump INNER JOIN [dbo_Final Action] ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#]) INNER JOIN tblCaseNumbers ON [dbo_Final Action].[Master ID] = tblCaseNumbers.ProspectKey
WHERE (([IDENT]=IIf(Right([ACCT],1)='D','D',IIf(Right([ACCT],1)='A','PLP', IIf(Right([ACCT],1)='B','SLP','M')))));
 

When I try to make it a delete query however:

DELETE tblCaseNumbers.*
FROM (tblCaseNumberDump INNER JOIN [dbo_Final Action] ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#]) INNER JOIN tblCaseNumbers ON [dbo_Final Action].[Master ID] = tblCaseNumbers.ProspectKey
WHERE (((tblCaseNumbers.Ident)=IIf(Right([ACCT],1)='D','D', IIf(Right([ACCT],1)='A','PLP',IIf(Right([ACCT],1)='B','SLP','M')))));
 

The delete won't work. I am assuming that it is because of the join? Unfortunately, I cannot change the table structure here. All relationships must remain intact. Can anyone help me on this? I would prefer not to do it with ADODB recordsets....

I am writing a series of queries here. The idea is to take data from a spreadsheet (tblCaseNumberDump) and put it in a usable format in tblCaseNumber. tblCaseNumberDump does not have the foreign key in it, so I am using the SSN as an identifier (it is unique in that table). In tblCaseNumbers, SSN is not unique. The key is a combination of the ProspectKey and Ident. The point of this delete query would be to delete any values in tblCaseNumbers where new data is coming.

Thanks,
Josh

[MMcCarthy]

What table is [ACCT] in? Try the following BTW, reordering the tables may solve the problem.

DELETE tblCaseNumbers.*
FROM tblCaseNumbers INNER JOIN (tblCaseNumberDump INNER JOIN [dbo_Final Action]
ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#])
ON tblCaseNumbers.ProspectKey=[dbo_Final Action].[Master ID])
WHERE tblCaseNumbers.Ident=
IIf(Right([ACCT],1)='D','D', 
IIf(Right([ACCT],1)='A','PLP',
IIf(Right([ACCT],1)='B','SLP','M')));
 

[jpatchak]

ACCT is in the tblCaseNumberDump table. I tried your query below and got the same error message "could not delete from specified tables"

Thanks.

What table is [ACCT] in? Try the following BTW, reordering the tables may solve the problem.

DELETE tblCaseNumbers.*
FROM tblCaseNumbers INNER JOIN (tblCaseNumberDump INNER JOIN [dbo_Final Action]
ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#])
ON tblCaseNumbers.ProspectKey=[dbo_Final Action].[Master ID])
WHERE tblCaseNumbers.Ident=
IIf(Right([ACCT],1)='D','D', 
IIf(Right([ACCT],1)='A','PLP',
IIf(Right([ACCT],1)='B','SLP','M')));
 

[MMcCarthy]

OK this is getting a little creative. Back up your table first in case it all goes wrong.

DELETE tblCaseNumbers.*
FROM tblCaseNumbers 
WHERE tblCaseNumbers.Ident=
IIf(Right([ACCT],1)='D','D', 
IIf(Right([ACCT],1)='A','PLP',
IIf(Right([ACCT],1)='B','SLP','M')))
AND tblCaseNumbers.ProspectKey=DLookup("[Master ID]","dbo_Final Action","[Primary SS#]=tblCaseNumberDump.SSN");