CODEWITHSUNDEEP
ms-accessms-access-2013
scholzr
scholzr

Reputation: 245

MS Access nested join error

I am working with Access 2013, and am having some issues with a nested join. I am afraid that I am not very proficient with nested joins, so the "Error in JOIN syntax" error message the Access is giving me is not helping much.

I have two tables:

Farms           RAMP
--------        --------
FarmID          RAMPID
Farm Name       FarmID
                Date Expires

There may be multiple RAMP entries for each Farm entry, and I would like to include only the most recent expiration date for each farm entry, or null if there is no entry. I am trying to use the following query, but Access is giving my an error saying that there is an error in my JOIN syntax:

SELECT Farms.FarmID, Farms.[Farm Name], [Expires]
FROM Farms 
    LEFT JOIN (SELECT TOP 1 RAMP.[Date Expires] FROM RAMP WHERE RAMP.[FarmID] = Farms.FarmID) AS Expires ON Farms.[FarmID] = RAMP.[FarmID]

Any suggestions as to what I am doing wrong?

Upvotes: 1

Views: 142

Answers (2)

HansUp
HansUp

Reputation: 97101

It looked to me like you were aiming for a correlated subquery. So, I offered the query below. However correlated subqueries can be slow. I think Young Bob offered a better approach. I'll leave this one just in case.

SELECT
    Farms.FarmID,
    Farms.[Farm Name],
    (
        SELECT TOP 1 RAMP.[Date Expires]
        FROM RAMP
        WHERE RAMP.[FarmID] = Farms.FarmID
    ) AS Expires
FROM Farms;

Allen Browne has useful tips for subqueries: Subquery basics and Surviving Subqueries

Upvotes: 1

Young Bob
Young Bob

Reputation: 743

It's probably objecting to the "ON FARMS.[FarmId] = RAMP.[FarmId]" clause because you are joining with Expires not RAMP. Anyway to achieve what you want i think you need to use a group by:

SELECT Farms.FarmID, Farms.[Farm Name], MAX(RAMP.[Expires]) AS Expires
FROM Farms 
LEFT JOIN RAMP ON RAMP.[FarmID] = Farms.FarmID
GROUP BY Farms.FarmID, Farms.[Farm Name]

Upvotes: 2

Related Questions