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
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.
Any advice would be appreciated! Let me know if I provide any more info!
Thanks for the reply. The website was very informative. But I'm not sure I want to do a full outer join. Ideally, it would be a left outer join nested in an inner join. The left outer join and the inner join work fine on their own, but when I combine them so that the inner join filters the outer join, I get the error message described above. Is there some way to accomplish this without a union?
SELECT Sum(LaborHours.LHours) AS SumOfLHours
FROM Jobs INNER JOIN LaborHours ON Jobs.ID=LaborHours.JobID
SELECT Items.Item, Items.IType, Items.IDescription, Items.[IU/M], Sum(LaborHours.LHours) AS SumOfLHours
FROM Items LEFT JOIN LaborHours ON Items.ID=LaborHours.ItemID
GROUP BY Items.Item, Items.IType, Items.IDescription, Items.[IU/M], SumOfLHours;
If (and only if) I correctly understand what you want to achieve, the query you need should be:
INNER JOIN (
SELECT Sum(LaborHours.LHours) AS SumOfLHours,
INNER JOIN LaborHours ON Jobs.ID = LaborHours.JobID
GROUP BY LaborHours.ItemID
) AS s
ON Items.ID = s.ItemID;
First off, thanks for the help so far! This forum is great.
I ran the code, and it works, but unfortunately it still doesn't get the results I want. I need the Left Join (I think) because the next step is to add a field for budgeted labor hours so it can be compared with the actual hours worked. That means there may be some discrepancies where there are hours budgeted, but no hours worked (yet) or maybe a situation where there are some hours worked for an item that wasn't budgeted. So my rationale was that if I display all the possible labor items and link those to the fields for labor hours and budgeted labor hours, I should be able to display the results for both independently. I tried the union query but I ran into some trouble because the fields have different numbers of records (not all the possible labor items are worked or budgeted for).
Again, I really appreciate your help! If you can think of anything else, please let me know. I'll get back to you on Monday when I have access to my computer again.
The budgeted labor hours comes from its own table, with the job id, item ids, and budgeted labor hours for each item. The labor hours table includes the job id, item ids, the date, and labor hours worked. I also have parent tables for jobs and items.