CODEWITHSUNDEEP
sqlms-access
Shane LeBlanc
Shane LeBlanc

Reputation: 2643

Issue with access inner join

I keep receiving a syntax error on this particular statement. 

SELECT tbl1.ProjectID, tbl1.EntryDate AS StartDate, tbl2.EntryDate AS EndDate, 
(tbl3.ChecklistDayMax - tbl3.ChecklistDayMin + 1) AS DaysAllotted, 
(SELECT ProjectPriority FROM project_master WHERE ProjectID = tbl1.ProjectID) AS Priority,
tbl3.MilestoneName,
IIF(Priority = 1, tbl3.BonusDaysFH, 
IIF(Priority = 2, tbl3.BonusDaysFM, 
IIF(Priority = 3, tbl3.BonusDaysFL))) AS BonusDaysAllotted
FROM (((checklist_entries AS tbl1
INNER JOIN checklist_entries AS tbl2 ON tbl1.ProjectID = tbl2.ProjectID)
INNER JOIN milestone_def AS tbl3 ON [@milestoneID] = milestone_def.MilestoneDefID)
INNER JOIN project_active_status AS tbl4 ON tbl1.ProjectID = project_active_status.ProjectID)
WHERE tbl1.ChecklistDay = tbl3.ChecklistDayMin
AND tbl2.ChecklistDay = tbl3.ChecklistDayMax
AND tbl4.ProjectIsOpen = FALSE;

The error says Syntax Error In Join Operation and then it highlights milestone_def right after the 2nd INNER JOIN. Funny thing is, if I switch this line...

INNER JOIN milestone_def AS tbl3 ON [@milestoneID] = milestone_def.MilestoneDefID)

with this line...

INNER JOIN milestone_def AS tbl3 ON [@milestoneID] = tbl3.MilestoneDefID)

I get the error Join Expression Not Supported and then it highlights...

[@milestoneID] = tbl3.MilestoneDefID)

But as you can see, in the first join...

INNER JOIN checklist_entries AS tbl2 ON tbl1.ProjectID = tbl2.ProjectID

I name it tbl2 and then use tbl2.ProjectID and the expression works just fine. Ultimately, I need to get this to work, regardless how how I name these things.

[@milestoneID] is a parameter passed into the query to match milestone_def.MilestoneDefID

Upvotes: 0

Views: 313

Answers (3)

Dave Sexton
Dave Sexton

Reputation: 11188

I think HansUp's answer is pointing you in the right direction. One other thing that I noticed is that you have a series of three nested IIFs and the final one only has a test and a true but is missing the false parameter. I thought that all three were compulsory.

Upvotes: -1

IMSoP
IMSoP

Reputation: 97996

[Expanded from comments.] This is just a hunch, as I don't have access to Access (ha ha), but your query currently specifies an INNER JOIN that doesn't actually relate the table to the rest of the query:

... 
INNER JOIN milestone_def AS tbl3
ON [@milestoneID] = milestone_def.MilestoneDefID
...

The ON clause references only an external variable, so isn't relevant to the JOIN operation, making this effectively a CROSS JOIN with a separate WHERE condition:

... 
CROSS JOIN milestone_def AS tbl3
...
WHERE [@milestoneID] = milestone_def.MilestoneDefID
...

Looking at the bottom of your query, you have the actual join conditions for this table in the WHERE clause; these should be swapped into the ON clause, so that it actually specifies the INNER JOIN condition:

... 
INNER JOIN milestone_def AS tbl3
ON tbl1.ChecklistDay = tbl3.ChecklistDayMin
AND tbl2.ChecklistDay = tbl3.ChecklistDayMax
...
WHERE [@milestoneID] = milestone_def.MilestoneDefID
...

It's certainly more logical that way, and it will possibly solve the problem Access's parser is having understanding your query.

Upvotes: 1

HansUp
HansUp

Reputation: 97131

Since the problem is with the joins, you would be wise to investigate the issue with a simpler query.

SELECT *
FROM
    ((checklist_entries AS tbl1
    INNER JOIN checklist_entries AS tbl2
    ON tbl1.ProjectID = tbl2.ProjectID)
    INNER JOIN milestone_def AS tbl3
    ON [@milestoneID] = milestone_def.MilestoneDefID)
    INNER JOIN project_active_status AS tbl4
    ON tbl1.ProjectID = project_active_status.ProjectID

Notice you have aliased the table names. Therefore you must use those aliases instead of the table names in the ON clauses. 

SELECT *
FROM
    ((checklist_entries AS tbl1
    INNER JOIN checklist_entries AS tbl2
    ON tbl1.ProjectID = tbl2.ProjectID)
    INNER JOIN milestone_def AS tbl3
    ON tbl1.[@milestoneID] = tbl3.MilestoneDefID)
    INNER JOIN project_active_status AS tbl4
    ON tbl1.ProjectID = tbl4.ProjectID

I don't know what [@milestoneID] is or where it comes from. My best guess is it's a field in checklist_entries, so I qualified it with the tbl1 alias.

Upvotes: 1

Related Questions