Outer Join Nested in Inner Join

[Stevens7]

Hi all,



I'm new to Access and I've hit a problem that has my head spinning. Any help would be greatly appreciated!

I'm trying to create an outer join query that displays a list of work items from a parent table and then displays the corresponding hours worked per item. It's important that I use an outer join for a step I'm going to be taking later.

The problem is that I need to filter the results by job. I've created a form with a combo box where the user can select the job for which they'd like to see the hours worked. Unfortunately, I'm having a heck of a time trying to structure the query.

Here's the SQL:

SELECT Items.Item, Items.IType, Items.IDescription, Items.[IU/M], Sum(LaborHours.LHours) AS SumOfHours
FROM Jobs INNER JOIN (Items LEFT OUTER JOIN LaborHours
ON Items.ID=LaborHours.ItemID)
ON Jobs.ID=LaborHours.JobID
GROUP BY Items.Item, Items.IType, Items.IDescription, Items.[IU/M], Jobs.Job
HAVING (((LaborHours.JobID) = [Forms]![JobCostReport].[JC1]));

This returns the error "Join Expression not supported". If I change the LEFT OUTER JOIN to INNER JOIN, it accepts the code.

For reference, the query is drawing from three separate tables: Jobs, Items, and LaborHours. I need it to be an outer join because later I'm going to add a field from a separate Budgeted Labor Hours table, and the records in that table don't always match up exactly with the actual LaborHours.

Any advice would be greatly appreciated! Let me know if I can provide any more info!

[TheShabz]

speaking strictly about the join syntax, if you're looking for:
Jobs ------ Items -----> LaborHours
try:
FROM (Jobs INNER JOIN Items ON Items.ID=Jobs.ID) LEFT JOIN LaborHours ON Items.ID = LaborHours.ItemID

[Stevens7]

Thanks for the reply! I gave the code a try, but unfortunately it didn't work.

I think the problem is because the Jobs and Items tables don't have any information that is linked. They are both parent tables that are referenced in the LaborHours table.