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