Results 1 to 2 of 2
Thread: Ambiguous Outer Joins (XP)
2008-07-15, 04:33 #1
- Join Date
- Mar 2006
- Whittier, California, USA
- Thanked 0 Times in 0 Posts
Ambiguous Outer Joins (XP)
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];
2008-07-15, 04:39 #2
- 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.