A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a parameter in a Union Query



 
 
Thread Tools Display Modes
  #1  
Old December 7th, 2009, 10:13 PM posted to microsoft.public.access.queries
John
external usenet poster
 
Posts: 2,649
Default 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  
Old December 8th, 2009, 01:23 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old December 8th, 2009, 03:43 PM posted to microsoft.public.access.queries
Dale Fye
external usenet poster
 
Posts: 2,651
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.