MS Access INNER JOIN most recent entry

Question

I'm having some trouble trying to get Microsoft Access 2007 to accept my SQL query but it keeps throwing syntax errors at me that don't help me correct the problem.

I have two tables, let's call them Customers and Orders for ease.

I need some customer details, but also a few details from the most recent order. I currently have a query like this:

SELECT c.ID, c.Name, c.Address, o.ID, o.Date, o.TotalPrice
FROM Customers c
INNER JOIN Orders o
ON c.ID = o.CustomerID
AND o.ID = (SELECT TOP 1 ID FROM Orders WHERE CustomerID = c.ID ORDER BY Date DESC)

To me, it appears valid, but Access keeps throwing 'syntax error's at me and when I hit OK, it selects a piece of the SQL text that doesn't even relate to it.

If I take the extra SELECT clause out it works but is obviously not what I need.

Any ideas?

cmon dude, what are the " 'syntax error's " ?? (not my downvote, BTW) — Mitch Wheat, Sep 02 '11 at 04:34
It seems like `CustomerID` in your subquery might be ambiguous. But, I'm not sure. — Josh Darnell, Sep 02 '11 at 04:37
Mitch, this is **Microsoft** Access. It doesn't have to tell you the cause of the error! It's literally, "Syntax error in your query" and that's it. — Nick Bedford, Sep 02 '11 at 04:43
Ick, I've seen that before. Then highlights something random. Thanks alot Access. — Josh Darnell, Sep 02 '11 at 04:44
Believe me, I'd be using SQL Server if I could. P.S. *Syntax error in query expression ''.* to be exact. — Nick Bedford, Sep 02 '11 at 04:48
But if you run it in the Access query grid, it will highlight the part of the SQL that is throwing the error. It doesn't do a very good job, as it highlights the first thing it doesn't understand, but it will very often at least tell you which clause the error is in (i.e., SELECT, FROM or WHERE). — David-W-Fenton, Sep 03 '11 at 21:11

score 1 · Answer 1 · answered Sep 02 '11 at 07:02

1

You cannot use AND in that way in MS Access, change it to WHERE. In addition, you have two reserved words in your column (field) names - Name, Date. These should be enclosed in square brackets when not prefixed by a table name or alias, or better, renamed.

SELECT c.ID, c.Name, c.Address, o.ID, o.Date, o.TotalPrice
FROM Customers c
INNER JOIN Orders o
ON c.ID = o.CustomerID
WHERE o.ID = (
     SELECT TOP 1 ID FROM Orders 
     WHERE CustomerID = c.ID ORDER BY [Date] DESC)

answered Sep 02 '11 at 07:02

Fionnuala

90,370
7
114
152

Doesn't work because the `WHERE` clause is used to filter the final result. – Nick Bedford Sep 04 '11 at 22:20
Is that not what your query is doing in a slightly different way? – Fionnuala Sep 05 '11 at 08:01

score 0 · Accepted Answer · answered Sep 06 '11 at 01:07

I worked out how to do it in Microsoft Access. You INNER JOIN on a pre-sorted sub-query. That way you don't have to do multiple ON conditions which aren't supported.

SELECT c.ID, c.Name, c.Address, o.OrderNo, o.OrderDate, o.TotalPrice
FROM Customers c
INNER JOIN (SELECT * FROM Orders ORDER BY OrderDate DESC) o
ON c.ID = o.CustomerID

How efficient this is another story, but it works...

MS Access INNER JOIN most recent entry

2 Answers2

Linked