falcondeer
Registered User.
- Local time
- Yesterday, 19:58
- Joined
- May 12, 2013
- Messages
- 101
Hi
I have made a crosstab out of a query with UNION ALL.
The query is this:
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg, tblDiagnosis.D_Level, tblDiagnosis.D_Quadrant
FROM tblDiagnosis
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg, tblDiagnosis.D_Level, tblDiagnosis.D_Quadrant
HAVING (((tblDiagnosis.D_CauseCateg) Not Like "") AND ((tblDiagnosis.D_Level) Not Like "") AND ((tblDiagnosis.D_Quadrant) Like "Lower"));
UNION ALL
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg2, tblDiagnosis.D_Level2, tblDiagnosis.D_Quadrant2
FROM tblDiagnosis
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg2, tblDiagnosis.D_Level2, tblDiagnosis.D_Quadrant2
HAVING (((tblDiagnosis.D_CauseCateg2) Not Like "") AND ((tblDiagnosis.D_Level2) Not Like "") AND ((tblDiagnosis.D_Quadrant2) Like "Lower"));
UNION ALL
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg3, tblDiagnosis.D_Level3, tblDiagnosis.D_Quadrant3
FROM tblDiagnosis
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg3, tblDiagnosis.D_Level3, tblDiagnosis.D_Quadrant3
HAVING (((tblDiagnosis.D_CauseCateg3) Not Like "") AND ((tblDiagnosis.D_Level3) Not Like "") AND ((tblDiagnosis.D_Quadrant3) Like "Lower"));
UNION ALL
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg4, tblDiagnosis.D_Level4, tblDiagnosis.D_Quadrant4
FROM tblDiagnosis
WHERE (((tblDiagnosis.D_CauseCateg4) Not Like "") AND ((tblDiagnosis.D_Level4) Not Like "") AND ((tblDiagnosis.D_Quadrant4) Like "Lower"));
and the Crosstab is this:
TRANSFORM Count(qryLowerLevelVsCauseCategory.MainDiagnosisID) AS CountOfMainDiagnosisID
SELECT qryLowerLevelVsCauseCategory.D_Level, Count(qryLowerLevelVsCauseCategory.MainDiagnosisID) AS [Total Of MainDiagnosisID]
FROM qryLowerLevelVsCauseCategory
GROUP BY qryLowerLevelVsCauseCategory.D_Level
PIVOT qryLowerLevelVsCauseCategory.D_CauseCateg;
Now how can make the crosstab ask for a start date and end date.
Thanks
I have made a crosstab out of a query with UNION ALL.
The query is this:
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg, tblDiagnosis.D_Level, tblDiagnosis.D_Quadrant
FROM tblDiagnosis
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg, tblDiagnosis.D_Level, tblDiagnosis.D_Quadrant
HAVING (((tblDiagnosis.D_CauseCateg) Not Like "") AND ((tblDiagnosis.D_Level) Not Like "") AND ((tblDiagnosis.D_Quadrant) Like "Lower"));
UNION ALL
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg2, tblDiagnosis.D_Level2, tblDiagnosis.D_Quadrant2
FROM tblDiagnosis
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg2, tblDiagnosis.D_Level2, tblDiagnosis.D_Quadrant2
HAVING (((tblDiagnosis.D_CauseCateg2) Not Like "") AND ((tblDiagnosis.D_Level2) Not Like "") AND ((tblDiagnosis.D_Quadrant2) Like "Lower"));
UNION ALL
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg3, tblDiagnosis.D_Level3, tblDiagnosis.D_Quadrant3
FROM tblDiagnosis
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg3, tblDiagnosis.D_Level3, tblDiagnosis.D_Quadrant3
HAVING (((tblDiagnosis.D_CauseCateg3) Not Like "") AND ((tblDiagnosis.D_Level3) Not Like "") AND ((tblDiagnosis.D_Quadrant3) Like "Lower"));
UNION ALL
SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg4, tblDiagnosis.D_Level4, tblDiagnosis.D_Quadrant4
FROM tblDiagnosis
WHERE (((tblDiagnosis.D_CauseCateg4) Not Like "") AND ((tblDiagnosis.D_Level4) Not Like "") AND ((tblDiagnosis.D_Quadrant4) Like "Lower"));
and the Crosstab is this:
TRANSFORM Count(qryLowerLevelVsCauseCategory.MainDiagnosisID) AS CountOfMainDiagnosisID
SELECT qryLowerLevelVsCauseCategory.D_Level, Count(qryLowerLevelVsCauseCategory.MainDiagnosisID) AS [Total Of MainDiagnosisID]
FROM qryLowerLevelVsCauseCategory
GROUP BY qryLowerLevelVsCauseCategory.D_Level
PIVOT qryLowerLevelVsCauseCategory.D_CauseCateg;
Now how can make the crosstab ask for a start date and end date.
Thanks