Showing all subrows in crosstab query

[Ragnar Midtskogen]

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 are:

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

 

[Michel Walsh]

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]

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

 

[Michel Walsh]

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