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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating a parameter in a Union Query
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. |
#2
|
|||
|
|||
Creating a parameter in a Union Query
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"; -- Build a little, test a little. "John" wrote: 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. |
#3
|
|||
|
|||
Creating a parameter in a Union Query
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_BirthDateAdd("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_BirthDateAdd("yyyy",-19,Date()), "Gestational 1-18 years", Date_of_Birth=DateAdd("yyyy",-19,Date()),"Gestational 19+ years", True,"Unknown"); -- ---- HTH Dale "John" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|