INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...This site is awesome!...Things I have been trying to figure out for weeks, I got the answer in hours!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

DELETE Query issues

Read More Threads Like This One

corchard (Programmer)

5 Jul 12 18:33

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 (Programmer)

6 Jul 12 0:05

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

CODE --> SQL

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 (Programmer)

6 Jul 12 8:34

or - if Name is your PK field - you can use your Select:

DELETE FROM 20120627CSV
WHERE Name IN( SELECT First([20120627CSV].Group) AS FirstOfGroup FROM 20120627CSV GROUP BY [20120627CSV].Group HAVING (((Count([20120627CSV].Group))=1)) )

Have fun.

---- Andy

dhookom (Programmer)

6 Jul 12 10:46

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 (Programmer)

6 Jul 12 11:06

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 (Programmer)

6 Jul 12 12:34

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)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL