Creating queries asking for counts

I have a database for our hospital diabetes center. We created a great set of
forms etc... to stream line our paperwork, now we need to create some
reports. I am trying to create queries to show:
Number of patients who have Type 1 diabetes, Number of patients who have
Type 2 diabetes, number of patients who have gestational diabetes 1-18 years
old and 19 years old. I can create a query for each one of these number of
patients and get a long list of each. As soon as I insert a count or other
criteria into the query Access comes back and tells me the expression is to
complicated.
We also ask patients to rate their amount of exercise on a scale of 1 - 5.
Again I am sort of asking for the same thing as above. How many patients
stated they were at a one, at a two etc..... I create a query and get along
list of all of the ones in a row, then the twos in a row etc... When I
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

If your data is 'normal', as using a single field to store the type of
diabetes then the query below will give you the idea how.

SELECT Sum(IIF([YourTable].[Type], = "1", 1,0)) AS CountType1,
Sum(IIF([YourTable].[Type], = "2", 1,0)) AS CountType2,
Sum(IIF([YourTable].[Gestational_Age], = "1-18", 1, 0)) AS [Count Gestational
Diabetes 1-18 years], Sum(IIF([YourTable].[Gestational_Age], = "19", 1, 0))
AS [Count Gestational Diabetes 19 years], Sum(IIF(Eexercise scale] = "1", 1,
0)) AS Exercise_Level_1, Sum(IIF(Eexercise scale] = "2", 1, 0)) AS
Exercise_Level_2, Sum(IIF(Eexercise scale] = "3", 1, 0)) AS Exercise_Level_3,
Sum(IIF(Eexercise scale] = "4", 1, 0)) AS Exercise_Level_4, Sum(IIF(Eexercise
scale] = "5", 1, 0)) AS Exercise_Level_5,
FROM YourTable;

--
Build a little, test a little.


"John" wrote:

I have a database for our hospital diabetes center. We created a great set of
forms etc... to stream line our paperwork, now we need to create some
reports. I am trying to create queries to show:
Number of patients who have Type 1 diabetes, Number of patients who have
Type 2 diabetes, number of patients who have gestational diabetes 1-18 years
old and 19 years old. I can create a query for each one of these number of
patients and get a long list of each. As soon as I insert a count or other
criteria into the query Access comes back and tells me the expression is to
complicated.
We also ask patients to rate their amount of exercise on a scale of 1 - 5.
Again I am sort of asking for the same thing as above. How many patients
stated they were at a one, at a two etc..... I create a query and get along
list of all of the ones in a row, then the twos in a row etc... When I
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

Karl
Thanks for the reply, however I continue to get an error message when I try
and run the query that asks for pararenthases around the sub query. I have
tryed everything I can think of and I still get the same error.
Also as a point of reference, the table we are using to query is titled
Diabetes and the field is Diabetes-Type. When the user enters data into the
table they click the drop down box and select either Gestational
pre-diabetes, Type 1 or Type 2. Also maybe I was not clear in my original
post. The dibetes type query and the Exercise query are two seperate queries.
However, both queries are very similar in programing. So if I get one I am
confident I can copy to the other.
Again thanks for your help!! I can write simple queries with my eyes closed,
but when it comes to building statements it is more than that!

"KARL DEWEY" wrote:

If your data is 'normal', as using a single field to store the type of
diabetes then the query below will give you the idea how.

SELECT Sum(IIF([YourTable].[Type], = "1", 1,0)) AS CountType1,
Sum(IIF([YourTable].[Type], = "2", 1,0)) AS CountType2,
Sum(IIF([YourTable].[Gestational_Age], = "1-18", 1, 0)) AS [Count Gestational
Diabetes 1-18 years], Sum(IIF([YourTable].[Gestational_Age], = "19", 1, 0))
AS [Count Gestational Diabetes 19 years], Sum(IIF(Eexercise scale] = "1", 1,
0)) AS Exercise_Level_1, Sum(IIF(Eexercise scale] = "2", 1, 0)) AS
Exercise_Level_2, Sum(IIF(Eexercise scale] = "3", 1, 0)) AS Exercise_Level_3,
Sum(IIF(Eexercise scale] = "4", 1, 0)) AS Exercise_Level_4, Sum(IIF(Eexercise
scale] = "5", 1, 0)) AS Exercise_Level_5,
FROM YourTable;

--
Build a little, test a little.


"John" wrote:

I have a database for our hospital diabetes center. We created a great set of
forms etc... to stream line our paperwork, now we need to create some
reports. I am trying to create queries to show:
Number of patients who have Type 1 diabetes, Number of patients who have
Type 2 diabetes, number of patients who have gestational diabetes 1-18 years
old and 19 years old. I can create a query for each one of these number of
patients and get a long list of each. As soon as I insert a count or other
criteria into the query Access comes back and tells me the expression is to
complicated.
We also ask patients to rate their amount of exercise on a scale of 1 - 5.
Again I am sort of asking for the same thing as above. How many patients
stated they were at a one, at a two etc..... I create a query and get along
list of all of the ones in a row, then the twos in a row etc... When I
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

Can you post the SQL of the query that causes the problem? It's hard to
guess what the problem might be without it.

I wonder about your use of a field named "Diabetes-Type"; unless your are
very careful about how you use it, MS Access might get confused and try to do
subtraction. I suggest you use Diabetes_Type instead.

John



John wrote:
Karl
Thanks for the reply, however I continue to get an error message when I try
and run the query that asks for pararenthases around the sub query. I have
tryed everything I can think of and I still get the same error.
Also as a point of reference, the table we are using to query is titled
Diabetes and the field is Diabetes-Type. When the user enters data into the
table they click the drop down box and select either Gestational
pre-diabetes, Type 1 or Type 2. Also maybe I was not clear in my original
post. The dibetes type query and the Exercise query are two seperate queries.
However, both queries are very similar in programing. So if I get one I am
confident I can copy to the other.
Again thanks for your help!! I can write simple queries with my eyes closed,
but when it comes to building statements it is more than that!

If your data is 'normal', as using a single field to store the type of
diabetes then the query below will give you the idea how.
[quoted text clipped - 25 lines]
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

Another possible approach would be a UNION ALL operation using appropriate
text constants as the 'category' for each count:

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
SELECT "Type 2 diabetes",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Type 2 diabetes"
UNION ALL
SELECT "Gestational diabetes 1-18 years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 18
UNION ALL
SELECT "Gestational diabetes 19+ years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 19
UNION ALL
SELECT "Exercise Rated 1",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 1
UNION ALL
SELECT "Exercise Rated 2",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 2
UNION ALL
SELECT "Exercise Rated 3",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 3
UNION ALL
SELECT "Exercise Rated 4",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 4
UNION ALL
SELECT "Exercise Rated 5",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 5;

Where Patients is the table name and Condition is a column of text data type
with the patient's medical condition, DateOfBirth is a column of date/time
data type with the patient's date of birth, and ExerciseRating is the column
of integer number data type with the patient's assessment of their exercise
taken.

The age of the patient is computed from their date of birth by the following
function, which should be pasted into a standard module in the database:

Public Function GetAge(varDob, Optional varDateAt)

Dim intYears As Integer

' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = VBA.Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then
intYears = intYears - 1
End If

GetAge = intYears
End If

End Function

The above query does assume a rather simply structured table, particularly
with regard to the Condition column. In a well-designed database this is
more likely to be in a related Conditions table, with a primary key
ConditionID say, which is referenced by a foreign key ConditionID column in
the Patients table, so these would need to be joined in each part of the
query as follows:

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients] INNER JOIN [Conditions]
ON [Patients].[ConditionID] = [Conditions].[ConditionID]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
and so on

Each part of the query could of course be restricted to a particular subset
of patients if necessary, e.g. to return patients admitted in a particular
month on the basis of an AdmissionDate column:

SELECT "Type 1 diabetes" As Category,
COUNT(*) AS [Number of Patients]
FROM [Patients] INNER JOIN [Conditions]
ON [Patients].[ConditionID] = [Conditions].[ConditionID]
WHERE [Condition] = "Type 1 diabetes"
AND YEAR([AdmissionDate]) = [Enter year admitted:]
AND MONTH([AdmissionDate]) = [Enter month admitted ( 1-12):]
UNION ALL
and so on

which would prompt for the year and month at runtime.

Ken Sheridan
Stafford, England

John wrote:
I have a database for our hospital diabetes center. We created a great set of
forms etc... to stream line our paperwork, now we need to create some
reports. I am trying to create queries to show:
Number of patients who have Type 1 diabetes, Number of patients who have
Type 2 diabetes, number of patients who have gestational diabetes 1-18 years
old and 19 years old. I can create a query for each one of these number of
patients and get a long list of each. As soon as I insert a count or other
criteria into the query Access comes back and tells me the expression is to
complicated.
We also ask patients to rate their amount of exercise on a scale of 1 - 5.
Again I am sort of asking for the same thing as above. How many patients
stated they were at a one, at a two etc..... I create a query and get along
list of all of the ones in a row, then the twos in a row etc... When I
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

Ken and everyone else
Thank You for your responses. However I am still having the same issue. When
I type in the expression that Ken suggested below I get a message saying the
alternate query should be enclosed in paranthesis or my syntax is wrong. I
have pasted the expression I typed below:
SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
SELECT "Type 2 diabetes",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Type 2 diabetes"
UNION ALL
SELECT "Gestational diabetes 1-18 years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 18
UNION ALL
SELECT "Gestational diabetes 19+ years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 19
UNION ALL
I replaced [Patients] with the real name of the table Diabetes and
[Condition] with the real name of the field Diabetes_Type. I am typing the
expression by right clicking in the Criteria row in the Query grid and
clicking build and typeing in the expression. Am I typing in the wrong
place??? Should the expression be typed in somewhere else?

"KenSheridan via AccessMonster.com" wrote:

Another possible approach would be a UNION ALL operation using appropriate
text constants as the 'category' for each count:

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
SELECT "Type 2 diabetes",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Type 2 diabetes"
UNION ALL
SELECT "Gestational diabetes 1-18 years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 18
UNION ALL
SELECT "Gestational diabetes 19+ years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 19
UNION ALL
SELECT "Exercise Rated 1",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 1
UNION ALL
SELECT "Exercise Rated 2",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 2
UNION ALL
SELECT "Exercise Rated 3",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 3
UNION ALL
SELECT "Exercise Rated 4",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 4
UNION ALL
SELECT "Exercise Rated 5",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 5;

Where Patients is the table name and Condition is a column of text data type
with the patient's medical condition, DateOfBirth is a column of date/time
data type with the patient's date of birth, and ExerciseRating is the column
of integer number data type with the patient's assessment of their exercise
taken.

The age of the patient is computed from their date of birth by the following
function, which should be pasted into a standard module in the database:

Public Function GetAge(varDob, Optional varDateAt)

Dim intYears As Integer

' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = VBA.Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then
intYears = intYears - 1
End If

GetAge = intYears
End If

End Function

The above query does assume a rather simply structured table, particularly
with regard to the Condition column. In a well-designed database this is
more likely to be in a related Conditions table, with a primary key
ConditionID say, which is referenced by a foreign key ConditionID column in
the Patients table, so these would need to be joined in each part of the
query as follows:

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients] INNER JOIN [Conditions]
ON [Patients].[ConditionID] = [Conditions].[ConditionID]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
and so on

Each part of the query could of course be restricted to a particular subset
of patients if necessary, e.g. to return patients admitted in a particular
month on the basis of an AdmissionDate column:

SELECT "Type 1 diabetes" As Category,
COUNT(*) AS [Number of Patients]
FROM [Patients] INNER JOIN [Conditions]
ON [Patients].[ConditionID] = [Conditions].[ConditionID]
WHERE [Condition] = "Type 1 diabetes"
AND YEAR([AdmissionDate]) = [Enter year admitted:]
AND MONTH([AdmissionDate]) = [Enter month admitted ( 1-12):]
UNION ALL
and so on

which would prompt for the year and month at runtime.

Ken Sheridan
Stafford, England

John wrote:
I have a database for our hospital diabetes center. We created a great set of
forms etc... to stream line our paperwork, now we need to create some
reports. I am trying to create queries to show:
Number of patients who have Type 1 diabetes, Number of patients who have
Type 2 diabetes, number of patients who have gestational diabetes 1-18 years
old and 19 years old. I can create a query for each one of these number of
patients and get a long list of each. As soon as I insert a count or other
criteria into the query Access comes back and tells me the expression is to
complicated.
We also ask patients to rate their amount of exercise on a scale of 1 - 5.
Again I am sort of asking for the same thing as above. How many patients
stated they were at a one, at a two etc..... I create a query and get along
list of all of the ones in a row, then the twos in a row etc... When I
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

.

First: That is the query and it should be entered into the SQL view not the
query design view. Second it had several syntax errors - which I hope I've
corrected below

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Diabetes]
WHERE Diabetes_Type = "Type 1 diabetes"
GROUP BY "Type 1 diabetes"

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

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

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

That said this is overly complicated way to do this. A simpler query might be
the following (again this would be entered in the QUERY SQL view. Although
unlike a UNION query it can be constructed in query design view.

SELECT Diabetes_Type
, Count(Diabetes_Type) as CountAll
, Count(IIF(GetAge(DateOfBirth)=18,1,null)) as Under19
, Count(IIF(GetAge(DateOfBirth=19),1,Null) as Over19
FROM Diabetes
WHERE Diabetes_Type in
("Type 1 diabetes","Type 2 diabetes","Gestational diabetes"))
GROUP BY Diabetes_Type



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John wrote:
Ken and everyone else
Thank You for your responses. However I am still having the same issue. When
I type in the expression that Ken suggested below I get a message saying the
alternate query should be enclosed in paranthesis or my syntax is wrong. I
have pasted the expression I typed below:
SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
SELECT "Type 2 diabetes",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Type 2 diabetes"
UNION ALL
SELECT "Gestational diabetes 1-18 years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 18
UNION ALL
SELECT "Gestational diabetes 19+ years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 19
UNION ALL
I replaced [Patients] with the real name of the table Diabetes and
[Condition] with the real name of the field Diabetes_Type. I am typing the
expression by right clicking in the Criteria row in the Query grid and
clicking build and typeing in the expression. Am I typing in the wrong
place??? Should the expression be typed in somewhere else?

"KenSheridan via AccessMonster.com" wrote:

Another possible approach would be a UNION ALL operation using appropriate
text constants as the 'category' for each count:

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
SELECT "Type 2 diabetes",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Type 2 diabetes"
UNION ALL
SELECT "Gestational diabetes 1-18 years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 18
UNION ALL
SELECT "Gestational diabetes 19+ years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 19
UNION ALL
SELECT "Exercise Rated 1",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 1
UNION ALL
SELECT "Exercise Rated 2",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 2
UNION ALL
SELECT "Exercise Rated 3",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 3
UNION ALL
SELECT "Exercise Rated 4",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 4
UNION ALL
SELECT "Exercise Rated 5",
COUNT(*) FROM [Patients]
WHERE [ExerciseRating] = 5;

Where Patients is the table name and Condition is a column of text data type
with the patient's medical condition, DateOfBirth is a column of date/time
data type with the patient's date of birth, and ExerciseRating is the column
of integer number data type with the patient's assessment of their exercise
taken.

The age of the patient is computed from their date of birth by the following
function, which should be pasted into a standard module in the database:

Public Function GetAge(varDob, Optional varDateAt)

Dim intYears As Integer

' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = VBA.Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then
intYears = intYears - 1
End If

GetAge = intYears
End If

End Function

The above query does assume a rather simply structured table, particularly
with regard to the Condition column. In a well-designed database this is
more likely to be in a related Conditions table, with a primary key
ConditionID say, which is referenced by a foreign key ConditionID column in
the Patients table, so these would need to be joined in each part of the
query as follows:

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients] INNER JOIN [Conditions]
ON [Patients].[ConditionID] = [Conditions].[ConditionID]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
and so on

Each part of the query could of course be restricted to a particular subset
of patients if necessary, e.g. to return patients admitted in a particular
month on the basis of an AdmissionDate column:

SELECT "Type 1 diabetes" As Category,
COUNT(*) AS [Number of Patients]
FROM [Patients] INNER JOIN [Conditions]
ON [Patients].[ConditionID] = [Conditions].[ConditionID]
WHERE [Condition] = "Type 1 diabetes"
AND YEAR([AdmissionDate]) = [Enter year admitted:]
AND MONTH([AdmissionDate]) = [Enter month admitted ( 1-12):]
UNION ALL
and so on

which would prompt for the year and month at runtime.

Ken Sheridan
Stafford, England

John wrote:
I have a database for our hospital diabetes center. We created a great set of
forms etc... to stream line our paperwork, now we need to create some
reports. I am trying to create queries to show:
Number of patients who have Type 1 diabetes, Number of patients who have
Type 2 diabetes, number of patients who have gestational diabetes 1-18 years
old and 19 years old. I can create a query for each one of these number of
patients and get a long list of each. As soon as I insert a count or other
criteria into the query Access comes back and tells me the expression is to
complicated.
We also ask patients to rate their amount of exercise on a scale of 1 - 5.
Again I am sort of asking for the same thing as above. How many patients
stated they were at a one, at a two etc..... I create a query and get along
list of all of the ones in a row, then the twos in a row etc... When I
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

.

John:

Its not necessary to include a constant in a GROUP BY clause, only column
names or expressions which include them.

Ken Sheridan
Stafford, England

John Spencer wrote:
First: That is the query and it should be entered into the SQL view not the
query design view. Second it had several syntax errors - which I hope I've
corrected below

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Diabetes]
WHERE Diabetes_Type = "Type 1 diabetes"
GROUP BY "Type 1 diabetes"

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

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

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

That said this is overly complicated way to do this. A simpler query might be
the following (again this would be entered in the QUERY SQL view. Although
unlike a UNION query it can be constructed in query design view.

SELECT Diabetes_Type
, Count(Diabetes_Type) as CountAll
, Count(IIF(GetAge(DateOfBirth)=18,1,null)) as Under19
, Count(IIF(GetAge(DateOfBirth=19),1,Null) as Over19
FROM Diabetes
WHERE Diabetes_Type in
("Type 1 diabetes","Type 2 diabetes","Gestational diabetes"))
GROUP BY Diabetes_Type

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ken and everyone else
Thank You for your responses. However I am still having the same issue. When
[quoted text clipped - 149 lines]
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Open the query designer and switch to SQL view and paste in the whole of the
SQL statement in place of what's there already.

If you adopt John's approach, which returns the counts for each category in
columns rather than rows, note that it will return the count of the under and
over 18 year olds for each type of diabetes, not just for Gestational
Diabetes as you specified in your original post. The irrelevant ones can
just be ignored of course, or you could use expressions such as:

IIF(Diabetes_Type = "Gestational Diabetes",
COUNT(IIF(GetAge(DateOfBirth)=18,1,NULL),"N/A")

To return N/A in the types 1 and 2 diabetes rows.

Ken Sheridan
Stafford, England

John wrote:
Ken and everyone else
Thank You for your responses. However I am still having the same issue. When
I type in the expression that Ken suggested below I get a message saying the
alternate query should be enclosed in paranthesis or my syntax is wrong. I
have pasted the expression I typed below:
SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Patients]
WHERE [Condition] = "Type 1 diabetes"
UNION ALL
SELECT "Type 2 diabetes",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Type 2 diabetes"
UNION ALL
SELECT "Gestational diabetes 1-18 years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 18
UNION ALL
SELECT "Gestational diabetes 19+ years",
COUNT(*) FROM [Patients]
WHERE [Condition] = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 19
UNION ALL
I replaced [Patients] with the real name of the table Diabetes and
[Condition] with the real name of the field Diabetes_Type. I am typing the
expression by right clicking in the Criteria row in the Query grid and
clicking build and typeing in the expression. Am I typing in the wrong
place??? Should the expression be typed in somewhere else?

Another possible approach would be a UNION ALL operation using appropriate
text constants as the 'category' for each count:
[quoted text clipped - 119 lines]
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

John and Ken
Again thank you for all your help!!! You guys are increadible!!!!! I am new
to access and have only written simple queries. I get now one final error.
When I type in the date of birth I receive the following error:
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
I need to show how many people had gestational diabetes from 0-18 years old
and how many people from 19 and older.
I have pasted the whole module below:
Public Function GetAge(varDob, Optional varDateAt)

Dim intYears As Integer

' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = VBA.Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then
intYears = intYears - 1
End If

GetAge = intYears
End If

End Function

Lastly - where can I enter a paramater to ask for a begining date and an end
date so I can limit the query to just a particular range?
Again thank you for all your help and Merry Christmas.


"KenSheridan via AccessMonster.com" wrote:

John:

Its not necessary to include a constant in a GROUP BY clause, only column
names or expressions which include them.

Ken Sheridan
Stafford, England

John Spencer wrote:
First: That is the query and it should be entered into the SQL view not the
query design view. Second it had several syntax errors - which I hope I've
corrected below

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Diabetes]
WHERE Diabetes_Type = "Type 1 diabetes"
GROUP BY "Type 1 diabetes"

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

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

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

That said this is overly complicated way to do this. A simpler query might be
the following (again this would be entered in the QUERY SQL view. Although
unlike a UNION query it can be constructed in query design view.

SELECT Diabetes_Type
, Count(Diabetes_Type) as CountAll
, Count(IIF(GetAge(DateOfBirth)=18,1,null)) as Under19
, Count(IIF(GetAge(DateOfBirth=19),1,Null) as Over19
FROM Diabetes
WHERE Diabetes_Type in
("Type 1 diabetes","Type 2 diabetes","Gestational diabetes"))
GROUP BY Diabetes_Type

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ken and everyone else
Thank You for your responses. However I am still having the same issue. When
[quoted text clipped - 149 lines]
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

.