Creating a parameter in a Union Query

[John]

I have, with the help of this newsgroup, created a union query. However I now
need to add a parameter to have it only show me the data for a specific
period of time.
I have pasted the union query sql below.
SELECT "Type 1" AS [Catagory],
COUNT(*) AS [Number of Patients]
FROM [Diabetes]
WHERE Diabetes_Type = "Type 1"
GROUP BY "Type 1"

UNION ALL
SELECT "Type 2",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Type 2"
GROUP BY "Type 2"

UNION ALL
SELECT "Gestational 1-18 years",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Gestational"
AND GetAge([Date_of_Birth]) <=18
GROUP BY "Gestational 1-18 years"

UNION ALL SELECT "Gestational 19+ years",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Gestational"
AND GetAge([Date_of_Birth]) >=19
GROUP BY "Gestational 19+ years";

As you can see it shows me all of the three different types of diabetes for
the whole database. I need to know how many Type 1 diabetes, Type 2 diabetes
and Gestational diabetes for the two age groups for say the month of January
2009. I tried adding the parameters to the top of the query, it gives me the
places to fill in the dates, but I still get the same numbers for the whole
database.

 

[KARL DEWEY]

Just add criteria to all elements like this --

SELECT "Type 1" AS [Catagory],
COUNT(*) AS [Number of Patients]
FROM [Diabetes]
WHERE Diabetes_Type = "Type 1" AND (DataDate Between CVDate([Enter start])
AND CVDate([Enter end])
GROUP BY "Type 1"

UNION ALL
SELECT "Type 2",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Type 2" AND (DataDate Between CVDate([Enter start])
AND CVDate([Enter end])
GROUP BY "Type 2"

UNION ALL
SELECT "Gestational 1-18 years",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Gestational" AND GetAge([Date_of_Birth]) <=18 AND
(DataDate Between CVDate([Enter start]) AND CVDate([Enter end])
GROUP BY "Gestational 1-18 years"

UNION ALL SELECT "Gestational 19+ years",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Gestational" AND GetAge([Date_of_Birth]) >=19 AND
(DataDate Between CVDate([Enter start]) AND CVDate([Enter end])
GROUP BY "Gestational 19+ years";

 

[Dale Fye]

Why use the Union query at all? Assuming that you only have these four
categories, you could try:

SELECT
Switch([Diabetes_Type]="Type 1","Type 1",
[Diabetes_Type]="Type 2", "Type 2",
Date_of_Birth<DateAdd("yyyy",-19,Date()),"Gestational 1-18 years",
Date_of_Birth>=DateAdd("yyyy",-19,Date()),"Gestational 19+ years",
True,"Unknown") AS Category, Count(*) AS CountOfID
FROM tbl_Diabetes
GROUP BY
Switch([Diabetes_Type]="Type 1","Type 1",
[Diabetes_Type]="Type 2","Type 2",
Date_of_Birth<DateAdd("yyyy",-19,Date()), "Gestational 1-18 years",
Date_of_Birth>=DateAdd("yyyy",-19,Date()),"Gestational 19+ years",
True,"Unknown");