DELETE Query issues 1

[corchard]

Hi guys, I have a table [20120627CSV] with 200k records in it and a Column called Group.

I need to delete all records that do not belong to more than one group.

e.g.
Name | Date | Group
---------------------
Record1 | date | 1
Record2 | date | 1
Record3 | date | 2
Record4 | date | 3
Record5 | date | 3
Record6 | date | 4

so I have a query:
SELECT First([20120627CSV].Group) AS FirstOfGroup FROM 20120627CSV GROUP BY [20120627CSV].Group HAVING (((Count([20120627CSV].Group))=1));

which correctly returns Record3 and Record6

Now I need to create a Delete query that would delete Record3 and Record6 in this scenario. I feel like I've tried everything but fear I am overlooking the obvious from staring at it too long.

Advice?

Cheers,

Chris


"Illegitimis non carborundum"
(don't let the b@st@rds get you down)

 

[dhookom]

Do you really have these field names? Do you understand NAME and DATE are reserved words?
Try

DELETE [20120627CSV].*, [20120627CSV].GROUP
FROM 20120627CSV
WHERE ((([20120627CSV].GROUP) In (SELECT [20120627CSV].[Group] 
FROM [20120627CSV] 
GROUP BY [20120627CSV].[Group] 
HAVING Count([20120627CSV].[Group])=1)));

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

or - if Name is your PK field - you can use your Select:
[tt][blue]
DELETE FROM 20120627CSV
WHERE Name IN([/blue] SELECT First([20120627CSV].Group) AS FirstOfGroup FROM 20120627CSV GROUP BY [20120627CSV].Group HAVING (((Count([20120627CSV].Group))=1)) [blue])[/blue][/tt]

Have fun.

---- Andy

 

[dhookom]

Andy,
I don't think your solution will work since you are equating the [Name] field to the [Group] field.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Oh, I see. You are right.
I was miss-reading "returns Record3 and Record6".

But my usual approach is: create SELECT statement of records I want to Delete, and use it in my DELETE statement.

Have fun.

---- Andy

 

[corchard]

Thanks Duane! This one worked! And for the record, no the field names weren't "Name" and "Date" ;-) Just there for example purposes. Group however IS a field name pumped out by another piece of software. I could change it, but if specified as .[Group] it seems to work.

Thanks for taking the time guys!

Chris

"Illegitimis non carborundum"
(don't let the b@st@rds get you down)