Creating queries asking for counts

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

 

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

 

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

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.

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!

 

[J_Goddard via AccessMonster.com]

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

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]

 

[KenSheridan via AccessMonster.com]

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]

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:

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!

--



.

 

[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?

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!

--



.

 

[KenSheridan via AccessMonster.com]

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

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]

 

[KenSheridan via AccessMonster.com]

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

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]

 

[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.

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

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!

--



.

 

[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.

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

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!

--



.

 

[KenSheridan via AccessMonster.com]

I think you've been clobbered by the newsgroup word-wrap demon! The lines:

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

Should be one unbroken line:

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

Unfortunately newsreaders break long lines of code into two separate lines,
which is sometimes not readily apparent.

To restrict a query to a date range defined by parameters you can enter the
following into the criteria row of the relevant date column in design view:

Between [Enter start date:] And [Enter end date:]

This will translate to an expression in the query's WHERE clause such as:

WHERE [YourDateField] Between [Enter start date:] And [Enter end date:]

With date/time parameters its always a good idea to declare them as such to
avoid their being misinterpreted as arithmetical expressions. This can be
done via the interface in design view or a PARAMETERS clause can be added to
the start of the query in SQL view:

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT etc

If using a UNION or UNION ALL query you only need declare the parameters once
at the start of the query, but you'd need to include them separately in the
WHERE clauses of each part of the UNION or UNION ALL operations.

One thing to note about a Between….And operation however is that the date
values must not include non-zero times of day or any such date/time values on
the last day will be missed. The best way to avoid this is to give the date
field a validation rule to allow only dates with zero times of day, but
otherwise, by defining the range in a different way any such date/time values
will be caught:

WHERE [YourDateField] >= [Enter start date:] And [YourDateField] < DateAdd
("d", 1,[Enter end date:])

This looks for dates on or after the start date and before the day following
the end date, so any date/time values on the final day are caught regardless
of their time of day element.

Ken Sheridan
Stafford, England

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.

John:

[quoted text clipped - 60 lines]