CODEWITHSUNDEEP
sqlms-access
bwri1407
bwri1407

Reputation: 13

MS Access keeps changing my SQL which it then can't read

Good morning / afternoon,

I have a very simple MS Access DB and I have a query that works fine until MS Access gets it's hands on it and makes it more "readable", which it then can't read. This is the query:

SELECT Checkout.*
FROM Checkout, (SELECT seatNumber, MAX(dateOut) AS lastOut FROM Checkout GROUP BY seatNumber) AS resultTable
WHERE Checkout.seatNumber=resultTable.seatNumber And resultTable.lastOut=Checkout.dateOut;

And this is what Access mangles it into:

SELECT Checkout.*
FROM Checkout, [SELECT seatNumber, MAX(dateOut) AS lastOut FROM Checkout GROUP BY seatNumber; ] AS resultTable
WHERE Checkout.seatNumber=resultTable.seatNumber And resultTable.lastOut=Checkout.dateOut;

And then carries on that it can't find the table.

I know there is no (easy) way to stop MS Access from being so helpful, but I was wondering if anyone can help me with a rework of the query. I kind of get set in a way of doing things and then when that doesn't work it's hard to break out of it. I was hoping a fresh and probably greatly more experienced eye than mine would be able to help me.

Thank you very much.

Upvotes: 1

Views: 290

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 51000

I generally encapsulate the logic of internal tables like resultTable as a separate VIEW (QUERY in MS Access parlance). If you do that, you should be able to refer to it without problem in your query.

You can also write this kind of query as 

SELECT * FROM Checkout C1
WHERE NOT EXISTS (SELECT * FROM CheckOut C2 
    WHERE C2.seatNumber = C1.seatNumber AND C2.dateOut > C1.dateOut);

which should not get mangled by Access.

If you've ever seen the movie The Producers there's a courtroom scene at the end that has Zero Mostel's character saying to Gene Wilder's "Leo! Don't help me!" I find myself saying the same thing frequently when using MS Office.

Upvotes: 3

Brad McKuhen
Brad McKuhen

Reputation: 16

I fired up Access and ran the following query in Access 2010 against a copy of Northwind.mdb, and it didn't get auto-corrected. What version of Access are you on?

SELECT orders.*
from orders, (select orderid, max(quantity) as maxq from [order details] group by orderid ) as results
where orders.orderid = results.orderid

Upvotes: 0

Related Questions