If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Showing all subrows in crosstab query
Calling all query gurus!
I have a crosstab query where I group on two fields, and I can get the query to show all the first field but not the second. By joining the crosstab query in a left join with a table with the diagnosis acronyms I can make it show all rows with the acronyms even if there are no records for a particular acronym, but I can't do the same for the categories. I tried doing a left join of the crosstab first with a table with categories, joining on category, then join that query in a left join with the acronyms table, joining on acronym, but it still does not show rows where there is no rows for a category. The queries a TRANSFORM Count(NPSpmsByDiagn.Diagnosis) SELECT NPSpmsByDiagn.Diagnosis, NPSpmsByDiagn.Category FROM NPSpmsByDiagn GROUP BY NPSpmsByDiagn.Diagnosis, NPSpmsByDiagn.Category PIVOT Format(NPSpmsByDiagn.DateRev,"mmm") IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); The underlying query, NPSpmsByDiagn, drawing data from the tables is: As you can see, I pull out diagnosis acronyms in Diagnosis and a string describing the category of specimen in Category. SELECT IIf([DiagnosisNP].Unsatisfactory = True, "UNS", IIf([Diagnosis NP].Positive = True, "POS", IIf([Diagnosis NP].Suspicious = True, "SUS", IIf([Diagnosis NP].Atypical = True, "ATP", IIf([Diagnosis NP].Negative = True, "NEG", ""))))) AS Diagnosis, IIf(([Specimen NP].Bronchial) OR ([Specimen NP].Sputum = True), "Respiratory", IIf(([Specimen NP].Pleural=True) OR ([Specimen NP].Ascitic=True) OR ([Specimen NP].Gastric=True) OR ([Specimen NP].Breast=True) OR ([Specimen NP].Urine=True) OR ([Specimen NP].FNA=True) OR ([Specimen NP].[Other Organ]=True), "Others", "")) AS Category FROM [Specimen NP] INNER JOIN [Diagnosis NP] ON [Specimen NP].[Accession Number]=[Diagnosis NP].[Accession Number] WHERE ( (([Diagnosis NP].[Final Diagnosis])=True) AND ((Year([Specimen NP].[Date Received]))=Forms.[Main Menu].[txtRptYear]) AND (("18" Left([Specimen NP].[Specimen Code], 2)))); Any comments would be appreciated, I am getting crosseyed from this crosstab problem (:-)). Ragnar |
#2
|
|||
|
|||
Showing all subrows in crosstab query
Hi,
Save the crosstab query, Qu1. Make a query that produces all the combination of Diagnosis and Category: SELECT Diagnosis, Category FROM AllDiagnosis, AllCategories save the query, Q2 Make a third query: SELECT Q2.*, Qu1.* FROM Q2 INNER JOIN Qu1 ON Q2.Diagnosis=Qu1.Diagnosis AND Q2.Category=Qu1.Category Hoping it may help, Vanderghast, Access MVP "Ragnar Midtskogen" wrote in message ... Calling all query gurus! I have a crosstab query where I group on two fields, and I can get the query to show all the first field but not the second. By joining the crosstab query in a left join with a table with the diagnosis acronyms I can make it show all rows with the acronyms even if there are no records for a particular acronym, but I can't do the same for the categories. I tried doing a left join of the crosstab first with a table with categories, joining on category, then join that query in a left join with the acronyms table, joining on acronym, but it still does not show rows where there is no rows for a category. The queries a TRANSFORM Count(NPSpmsByDiagn.Diagnosis) SELECT NPSpmsByDiagn.Diagnosis, NPSpmsByDiagn.Category FROM NPSpmsByDiagn GROUP BY NPSpmsByDiagn.Diagnosis, NPSpmsByDiagn.Category PIVOT Format(NPSpmsByDiagn.DateRev,"mmm") IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); The underlying query, NPSpmsByDiagn, drawing data from the tables is: As you can see, I pull out diagnosis acronyms in Diagnosis and a string describing the category of specimen in Category. SELECT IIf([DiagnosisNP].Unsatisfactory = True, "UNS", IIf([Diagnosis NP].Positive = True, "POS", IIf([Diagnosis NP].Suspicious = True, "SUS", IIf([Diagnosis NP].Atypical = True, "ATP", IIf([Diagnosis NP].Negative = True, "NEG", ""))))) AS Diagnosis, IIf(([Specimen NP].Bronchial) OR ([Specimen NP].Sputum = True), "Respiratory", IIf(([Specimen NP].Pleural=True) OR ([Specimen NP].Ascitic=True) OR ([Specimen NP].Gastric=True) OR ([Specimen NP].Breast=True) OR ([Specimen NP].Urine=True) OR ([Specimen NP].FNA=True) OR ([Specimen NP].[Other Organ]=True), "Others", "")) AS Category FROM [Specimen NP] INNER JOIN [Diagnosis NP] ON [Specimen NP].[Accession Number]=[Diagnosis NP].[Accession Number] WHERE ( (([Diagnosis NP].[Final Diagnosis])=True) AND ((Year([Specimen NP].[Date Received]))=Forms.[Main Menu].[txtRptYear]) AND (("18" Left([Specimen NP].[Specimen Code], 2)))); Any comments would be appreciated, I am getting crosseyed from this crosstab problem (:-)). Ragnar |
#3
|
|||
|
|||
Showing all subrows in crosstab query
Thank you Michel,
That worked! But I had to make the joins to the crosstab query left joins, because some diagnoses and some categories are not returned by that query. I did not think of creating all combinations of Diagnosis and Category first, then doing the join. Now that seems the obvious solution. Made my day! Ragnar |
#4
|
|||
|
|||
Showing all subrows in crosstab query
Hi,
I though of an LEFT join and I typed an INNER one, glad you read in my mind, not what I typed... :-) Vanderghast, Access MVP "Ragnar Midtskogen" wrote in message ... Thank you Michel, That worked! But I had to make the joins to the crosstab query left joins, because some diagnoses and some categories are not returned by that query. I did not think of creating all combinations of Diagnosis and Category first, then doing the join. Now that seems the obvious solution. Made my day! Ragnar |
Thread Tools | |
Display Modes | |
|
|