Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Whittier, California, USA
    Thanked 0 Times in 0 Posts

    Ambiguous Outer Joins (XP)

    Hello All,

    My boss wants me to create a query that looks more like a spreadsheet than what I'm used to doing, with the first 2 columns sorting by doctor then subtype, and then columns for Jan-June. I created separate queries for each month and a query to make the final table. The problem is it only shows results where the subtype is present in all 6 months. I changed to the query below to include all records of the subtype, but then get an Ambiguous Outer Joins error message. The help file says to split this into 2 queries, but I don't know how and may be off in left field anyway. Can anybody help me with this?



    SELECT [LName] & ", " & [SdFName] AS Faculty, tlstFClassCat.Descr AS [Fin Class], qryKelPts01.CountOfPtId AS Jan, qryKelPts02.CountOfPtId AS Feb, qryKelPts03.CountOfPtId AS March, qryKelPts04.CountOfPtId AS April, qryKelPts05.CountOfPtId AS May, qryKelPts06.CountOfPtId AS June
    FROM ((((((tblFaculty INNER JOIN qryKelPts01 ON tblFaculty.FacId = qryKelPts01.FacId) INNER JOIN qryKelPts02 ON tblFaculty.FacId = qryKelPts02.FacId) INNER JOIN qryKelPts03 ON tblFaculty.FacId = qryKelPts03.FacId) INNER JOIN qryKelPts04 ON tblFaculty.FacId = qryKelPts04.FacId) INNER JOIN qryKelPts05 ON tblFaculty.FacId = qryKelPts05.FacId) INNER JOIN qryKelPts06 ON tblFaculty.FacId = qryKelPts06.FacId) RIGHT JOIN tlstFClassCat ON (qryKelPts06.Cat = tlstFClassCat.FClassCatId) AND (qryKelPts05.Cat = tlstFClassCat.FClassCatId) AND (qryKelPts04.Cat = tlstFClassCat.FClassCatId) AND (qryKelPts03.Cat = tlstFClassCat.FClassCatId) AND (qryKelPts02.Cat = tlstFClassCat.FClassCatId) AND (qryKelPts01.Cat = tlstFClassCat.FClassCatId)
    ORDER BY [LName] & ", " & [SdFName];

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Ambiguous Outer Joins (XP)

    Instead of creating all those separate queries, try a crosstab query with doctor and subtype as row headings, month as column heading and Ptld as value field (with Count as summary function).
    The easiest way to create a crosstab query is to click New in the Queries section of the database and then select Crosstab Query Wizard.

Posting Permissions

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