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!

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

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 wrote:
> 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
>>
>> .
>>