PDA

View Full Version : Showing all subrows in crosstab query


Ragnar Midtskogen
May 25th, 2004, 11:34 PM
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
May 26th, 2004, 11:39 AM
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 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
>
>
>

Ragnar Midtskogen
May 26th, 2004, 04:51 PM
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
May 26th, 2004, 08:16 PM
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
>
>

Google