Simple SQL Count Query? 2

[jimlee]

I have a table (Results) that contains two columns ('Acc' and 'Rec'). The Acc column can have duplicate entries.
Each Acc will either have a currency assigned or a default of 999 or both.

For example:

Acc Rec
--- ---
100 GBP
100 999
200 AUD
300 999

What I'm trying to do is write a line of SQL that deletes the entry of 999 if the Acc is duplicated, but not delete the Acc if no true currency exists.

For example Results:

Acc Rec
--- ---
100 GBP
200 AUD
300 999

Acc 100 with a default (999) was not valid as it already has a true currency of GBP.

I know this is probably very simple but it's been killing me. Any help would be much appreciated!

Rgds,
Si


jimlad

 

[dhookom]

This might be slow if you have lots of records but should work:

DELETE Results.*
FROM Results
WHERE Rec="999" AND 
DCount("*","Results","Acc=" & [Acc] & " AND Rec<>'999'")>0;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jimlee]

Hi dhookom,

Thanks for the reply, I have 1 question, what is [Acc] referencing in your example: "Acc=" & [Acc]?

When I try to run this code it give me an 'object required' error. There is no user input for 'Acc', the 'Acc' within the DCount needs to be checking the same 'Acc' as the SQL is checking at that time (from the table). Is this possible.



jimlad

 

[PHV]

Another way:
DELETE FROM Results AS A
WHERE A.Rec='999' AND EXISTS (SELECT * FROM Results AS B WHERE B.Rec<>'999' AND B.Acc=A.Acc);

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

 

[dhookom]

I would use PHV's suggestion. I had thought about the subquery but found they often aren't updateable. Using PHV's solution would require the addition of an "*":

DELETE * FROM Results AS A
WHERE A.Rec='999' AND EXISTS (SELECT * FROM Results AS B WHERE B.Rec<>'999' AND B.Acc=A.Acc);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jimlee]

Thankyou very much PHV and dhookom. Your one line SQL statement replaced several of mine.

jimlad