How to get Crosstab to ask for [Start Date:] and [End Date:] (1 Viewer)

falcondeer

Registered User.
Local time
Yesterday, 18: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
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:58
Joined
Sep 21, 2011
Messages
14,048
Use a form would be the best way, but if you put the criteria in and it is not populated, Access will prompt for it
 

falcondeer

Registered User.
Local time
Yesterday, 18:58
Joined
May 12, 2013
Messages
101
Use a form would be the best way, but if you put the criteria in and it is not populated, Access will prompt for it
Sorry, I don't get it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:58
Joined
Sep 21, 2011
Messages
14,048
Sorry, I don't get it.
What don't you get?
How to set criteria, how to create a form?

As you have all those Unions I expect if you just put criteria like [Start Date] and [End Date] it will likely prompt you for them on every select, so that will, be 8 prompts, so best use a form to get the dates and refer to those controls as criteria in the query.

Could also use TempVars, but you still need criteria, in addition to what you have now.

Also have a look at Similar Threads below this thread.

Edit: The date criteria is for the Union query not the Crosstab.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:58
Joined
Feb 19, 2013
Messages
16,553
looks to me like your tblDiagnosis table is incorrectly structured (feels like an excel structure), necessitating the use if a union query. So basically you have built a rod for your own back. If it was structured correctly, your requirement would be easily resolved. Why are you grouping when you are not aggregating? Why are you using like when you are not using wild cards?

you could try replacing tblDiagnosis in your union query with another query called say qryDiagnosis

SELECT *
FROM tblDiagnosis
WHERE diagDate BETWEEN [Enter Start Date] AND [Enter End Date]

but personally I would address your table design before going further
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:58
Joined
Sep 12, 2006
Messages
15,614
in your query where you have the date, put in the criteria

between [startdate] and [enddate]
your could even hardcode them between #01/12/2020# and #31/12/2020#
(note that access will try to trap you, and might treat the first of these as Jan 12th, not 1st December.)
Anyway, hardcoding is a good way of testing that the query. actually works.

If these dates aren't known from the query source, you will be prompted to enter them.
It's better to populate startdate and enddate from within the database, eg a form - but that's the idea.
 

falcondeer

Registered User.
Local time
Yesterday, 18:58
Joined
May 12, 2013
Messages
101
in your query where you have the date, put in the criteria

between [startdate] and [enddate]
your could even hardcode them between #01/12/2020# and #31/12/2020#
(note that access will try to trap you, and might treat the first of these as Jan 12th, not 1st December.)
Anyway, hardcoding is a good way of testing that the query. actually works.

If these dates aren't known from the query source, you will be prompted to enter them.
It's better to populate startdate and enddate from within the database, eg a form - but that's the idea.

Now I have the date in my query as follows:


SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg, tblDiagnosis.D_Level, tblDiagnosis.D_Quadrant, tblClinic.Clinic_Date
FROM tblDiagnosis INNER JOIN tblClinic ON tblDiagnosis.MainDiagnosisID = tblClinic.MainDiagnosisID
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg, tblDiagnosis.D_Level, tblDiagnosis.D_Quadrant, tblClinic.Clinic_Date
HAVING (((tblDiagnosis.D_CauseCateg) Not Like "") AND ((tblDiagnosis.D_Level) Not Like "") AND ((tblDiagnosis.D_Quadrant) Like "Lower") AND ((tblClinic.Clinic_Date) Between [From:] And [To:]));

UNION ALL

SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg2, tblDiagnosis.D_Level2, tblDiagnosis.D_Quadrant2, tblClinic.Clinic_Date
FROM tblDiagnosis INNER JOIN tblClinic ON tblDiagnosis.MainDiagnosisID = tblClinic.MainDiagnosisID
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg2, tblDiagnosis.D_Level2, tblDiagnosis.D_Quadrant2, tblClinic.Clinic_Date
HAVING (((tblDiagnosis.D_CauseCateg2) Not Like "") AND ((tblDiagnosis.D_Level2) Not Like "") AND ((tblDiagnosis.D_Quadrant2) Like "Lower") AND ((tblClinic.Clinic_Date) Between [From:] And [To:]));

UNION ALL

SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg3, tblDiagnosis.D_Level3, tblDiagnosis.D_Quadrant3, tblClinic.Clinic_Date
FROM tblDiagnosis INNER JOIN tblClinic ON tblDiagnosis.MainDiagnosisID = tblClinic.MainDiagnosisID
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg3, tblDiagnosis.D_Level3, tblDiagnosis.D_Quadrant3, tblClinic.Clinic_Date
HAVING (((tblDiagnosis.D_CauseCateg3) Not Like "") AND ((tblDiagnosis.D_Level3) Not Like "") AND ((tblDiagnosis.D_Quadrant3) Like "Lower") AND ((tblClinic.Clinic_Date) Between [From:] And [To:]));

UNION ALL SELECT tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg4, tblDiagnosis.D_Level4, tblDiagnosis.D_Quadrant4, tblClinic.Clinic_Date
FROM tblDiagnosis INNER JOIN tblClinic ON tblDiagnosis.MainDiagnosisID = tblClinic.MainDiagnosisID
GROUP BY tblDiagnosis.MainDiagnosisID, tblDiagnosis.D_CauseCateg4, tblDiagnosis.D_Level4, tblDiagnosis.D_Quadrant4, tblClinic.Clinic_Date
HAVING (((tblDiagnosis.D_CauseCateg4) Not Like "") AND ((tblDiagnosis.D_Level4) Not Like "") AND ((tblDiagnosis.D_Quadrant4) Like "Lower") AND ((tblClinic.Clinic_Date) Between [From:] And [To:]));
 

Users who are viewing this thread

Top Bottom