Creating queries asking for counts

J

John

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!
 
K

KARL DEWEY

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;
 
J

John

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 said:
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 said:
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!
Click to expand...
Click to expand...
 
J

John

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 said:
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
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!
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
J

John Spencer

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
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 said:
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
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!
Click to expand...
--
Message posted via AccessMonster.com


.
Click to expand...
Click to expand...
 
J

John

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 said:
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 said:
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
Click to expand...
[quoted text clipped - 149 lines]
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
J

John Spencer

I don't see any error there except a wrapping error where the "THEN" wrapped
to the next line. It should be on the same line as

If varDateAt < DateSerial(Year(varDateAt), Month(varDob), Day(varDob))

Public Function GetAge(varDob, Optional varDateAt)
'Calculates the age in years based on an input date
'or the current date if there is no input date (varDateAt
Dim intYears As Integer

' return age 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

I would be passing a specific date in for age calculation. Otherwise your
numbers are going to change as time goes on. In a year the 18 year old will
be 19 (as a matter of fact tomorrow could be an 18 year olds date of birth and
the figures will then change if you ran this tomorrow).

Do you have a diagnosis date for when the condition was diagnosed? Or some
other static date such as a record created date or ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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 said:
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!
Click to expand...
--
Message posted via AccessMonster.com


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a parameter in a Union Query 2
Query criteria 9
Filtering records for 2 types of dates 10
Help on Counting and grouping distinct records 3
complex filter and calculations in access 17
"Use of OR condition in UPDATE Query" 2
Query to see what percentage of people scored a specific number 1
Multi-Value Field Query 0

Top