CODEWITHSUNDEEP
sqlms-accesssubquery
zach
zach

Reputation: 1361

MS Access: "Not In" is not working correctly

MS ACCESS SQL

DELETE * 
FROM Lane_Details
WHERE Lane_Details.Week not in(SELECT DISTINCT TOP 3 Lane_Details.Week
           FROM Lane_Details
WHERE Lane_Details.Week IS NOT NULL
ORDER BY Week DESC; )

DESIRED OUTCOME

What I need this to do is delete anything out of the table that is 4 weeks old or older.

I have been having trouble with the not in. it cause Access to completely lock up. I am not sure what the correct syntax should be for this, and I know that I can write it with a left outer join but I don't know how to do that.

DATA

ID            Lane  Time                        Week
6213214 83198524    4/6/2012 12:31:00 AM    201315
6213183 61780698    4/6/2012 12:31:00 AM    201311
6213201 11145552    4/6/2012 12:31:00 AM    201315
6213202 82391025    4/6/2012 12:31:00 AM    201314
6213203 11149012    4/6/2012 12:31:00 AM    201311
6213204 11140048    4/6/2012 12:31:00 AM    201311
6213205 83198524    4/6/2012 12:31:00 AM    201316
6213207 61625652    4/6/2012 12:31:00 AM    201316
6213210 61625652    4/6/2012 12:31:00 AM    201311
6637195 36166433    5/1/2012 8:25:00 AM           201314
6637206 77222091    5/1/2012 10:50:00 AM    201314

With this data, I would want to delete any row that has Week 201311. I want to keep 201314, 201315, and 201316.

The subquery will run on this table, and grab the top 3 dates (14, 15, 16) in this case. I then say "not in" to delete all the 11's.

Upvotes: 1

Views: 643

Answers (4)

zach
zach

Reputation: 1361

Thanks for all of the help. I finally realized that it wasn't my query, it just ran really slow. I think it was a performance issue. I decided to break the query down into a few different ones. It is working much better now. i eventually found this website and came to that conclusion.

VBA isnt the best place to do sub queries.

Upvotes: 1

Saic Siquot
Saic Siquot

Reputation: 6513

please remove the semicolon inside the subquery and create an index on week field. the query should work fine.

Upvotes: 0

Richard Spencer
Richard Spencer

Reputation: 631

What I need this to do is delete anything out of the table that is 4 weeks old or older.

Can you not do something like :-

DELETE * FROM Lane_Details where Week<=datepart("yyyy",dateadd("w",-4,now())) & datepart("ww",dateadd("w",-4,now()))

You may need to convert Week to a string but that would do the trick.

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

I'm not sure how well Access handles a self-reference in the WHERE clause on deleting. But you could do something like this instead to avoid the subquery:

DELETE FROM Lane_Details
WHERE Week <= (THIS WEEK - 4 WEEKS)

Where "THIS WEEK - 4 WEEKS" is a relevant expression to represent the end date before which you want to delete rows.

Upvotes: 0

Related Questions