Results 1 to 11 of 11

Thread: OUTER JOIN woes

  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Unanswered: OUTER JOIN woes

    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.

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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Unfortunately, Access doesn't include the ability to return a full outer join between two tables. There is a solution, though. See: Full outer join in Access
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    6

    Question

    Hi Sinndho,

    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?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you post the two queries that actually work when used alone?
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    6
    Sure.


    SELECT Sum(LaborHours.LHours) AS SumOfLHours
    FROM Jobs INNER JOIN LaborHours ON Jobs.ID=LaborHours.JobID
    HAVING (((LaborHours.JobID)=[Forms]![JobCostReport].[JC1]));



    and



    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;

    Hope that helps!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'll need a little time to analyze these, but I can already tell that the first query should probably be:
    Code:
    SELECT Sum(LaborHours.LHours) AS SumOfLHours
    FROM Jobs INNER JOIN LaborHours ON Jobs.ID=LaborHours.JobID
    WHERE LaborHours.JobID=[Forms]![JobCostReport].[JC1];
    There is no need for a HAVING clause here, the filtering of the selection can be done in the SELECT part of the query. HAVING would only make sense if the filtering was made on Sum(LaborHours.LHours)

    - WHERE performs the filtering before performing any grouping (GROUP BY clause) or aggregate function (SUM, MIN, MAX, AVG, etc.)
    - HAVING performs the filtering after these.

    Also, is there a precise reason why you perform a LEFT JOIN in the second query?

    More soon...
    Have a nice day!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If (and only if) I correctly understand what you want to achieve, the query you need should be:
    Code:
    SELECT Items.Item, 
           Items.IType, 
           Items.IDescription, 
           Items.[IU/M], 
           s.SumOfLHours
        FROM Items 
        INNER JOIN (
                    SELECT Sum(LaborHours.LHours) AS SumOfLHours, 
                           LaborHours.ItemID
                        FROM Jobs 
                        INNER JOIN LaborHours ON Jobs.ID = LaborHours.JobID
                        WHERE LaborHours.JobID=[Forms]![JobCostReport].[JC1]
                        GROUP BY LaborHours.ItemID
                   ) AS s
            ON Items.ID = s.ItemID;
    Have a nice day!

  8. #8
    Join Date
    Oct 2011
    Posts
    6
    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.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From which table comes [budgeted labor hours] ?
    Have a nice day!

  10. #10
    Join Date
    Oct 2011
    Posts
    6
    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.

  11. #11
    Join Date
    Oct 2011
    Posts
    6
    Haven't heard from anyone in a bit. Anyone have advice?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •