Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ambiguous Outer Join Problem (Access 2K)

    I am maintaining a metrics database for a S/W development organization in which work is broken up into work items (projects) and tasks. Each user then selects a WI and a task and enters the time spent. A report is produced monthly for the totals for each task and work item for the previous month, showing estimates and actuals for the month and the Block (our S/W is released yearly in blocks). My supervisor wants to see the report with tasks included aganst which no work was performed in a given month. In other words, show me all the tasks with their estimates and actuals even though the total for the month of the report is zero.

    Prior to this request, here is the SQL I used as the data source for the report. Note that the date range and Block are passed in by a filter at report time, based on user selections from a calling form.

    SELECT [LastName] & ", " & [FirstName] AS UserName,
    tblTimeSpent.Date,
    tblWorkItems.WorkItem,
    tblWorkItems.Block,
    tblWorkItems.SortPos AS WISortPos,
    tblTasks.Task,
    tblTasks.SortPos AS TaskSortPos,
    tblTimeSpent.Hours,
    tblTimeSpent.UserID,
    nz(DLookUp("SumOfTimeEstimate",
    "qryTimeEstimates",
    "[WorkItem] = '" & [WorkItem] & "' AND [Task] = '" & [Task] & "'"),0) AS Estimate
    FROM tblWorkItems
    INNER JOIN (tblTasks INNER JOIN (tblUsers INNER JOIN tblTimeSpent
    ON tblUsers.UserName = tblTimeSpent.UserID)
    ON tblTasks.TaskID = tblTimeSpent.TaskID)
    ON tblWorkItems.WorkItemID = tblTimeSpent.WorkItemID;

    The problem here is that it only shows tasks that have time logged against them for the month in question. I thought it would be simple enough to change the task linkage to an outer join, but I get an Ambiguous Outer Join Error when I attempt this. Can anybody give me a hint at to what I might be doing wrong and how I can accomplish what is needed?

    Thanks for the help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ambiguous Outer Join Problem (Access 2K)

    You'll have to make all joins into outer joins: include ALL records from tblTasks in the join between tblTasks and tblTimeSpent, include ALL records from tblTimeSpent in the join between tblTimeSpent and tblUsers and also in the join between tblTimeSpent and tblWorkItems.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ambiguous Outer Join Problem (Access 2K)

    I tried this but still get the same error. Looking at the SQL, it looks like it changed all the "INNER" joins to "LEFT" joins.

    Any other thoughts?

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ambiguous Outer Join Problem (Access 2K)

    Yes, outer joins are either LEFT JOINs or RIGHT JOINs. Keep the join between tblTasks and tblTimeSpent as it is (include ALL records from tblTasks) and experiment with the options for the other joins. If there is no combination that works, there is something wrong with your table design. Do you have a primary key in each of the tables, except perhaps tblTimeSpent?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ambiguous Outer Join Problem (Access 2K)

    Thanks for the help. Unfortunately, I wasn't able to figure this one out. All the tables have primary keys and monkeying with the join properties didn't yield any success.

    However, I reasoned that the data I was trying to report was only marginally similar, so I put a new report together to cover the Block totals and report the monthly totals separately. Fortunately, my supervisor saw it the same way. The result, I think, is better that the original approach ever hoped to be.

    Thanks again for your help.

Posting Permissions

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