A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Showing all subrows in crosstab query



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2004, 11:34 PM
Ragnar Midtskogen
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 11:39 AM
Michel Walsh
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 04:51 PM
Ragnar Midtskogen
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 08:16 PM
Michel Walsh
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.