Better way to Group Ages?

[Janna]

Not sure the best way to accomplish this. ANY help is appreciated.

My database has a table called tblClient. In the table I have the following
fields ID, FirstName, LastName, DOB and DateAppt. Thanks to help from the
Access Forms Coding, using the DOB, I can calculate the age of the client in
years from the date of initial appointment [DateAppt] using the following
code in a query that refers to a function called AgeYears

AgeInYears: AgeYears([Dob],[DateAppt]

I now need to break the clients down into age groups, by years. For
example, I need to know in 2003 how many were 15 years or younger and how
many were over the age of 15, the same for 2004 and 2005. I’d really like to
be able to do this by using the values that I calculated above in my
AgeInYears field, but I’m not having any luck.

If I put an additional field into tblClient called AgeofYouth and manually
type in the age.

I can accomplish what I want by using a multi-table query, with a second
table having fields:
Age Group Name 15AndYounger Over15
Minimum 0 16
Maximum 15 20

Into the same query, I add my tblClient, using the ID and AgeofYouth fields.
On the ID Total field, I use “Count†and the Ageof Youth Total field, I use
“Where†and in the Criteria I put “Between [Minimum] and [Maximum]

It gives me the values I want, it’s just not a very clever way of doing
it—here I have the system calculate the age, but I can’t seem to use the
value, once I have it, to break down the clients into groups without manually
entering the calculated age into an additional field. There’s got to be a
better way!!

 

[peregenem]

Janna wrote:

There are a few things that are not clear to me from your spec.

I need to know in 2003 how many were 15 years or younger and how
many were over the age of 15

For someone whose 15th birthday fell in 2003, do you want to count them
in both age groups?

I'd really like to
be able to do this by using the values that I calculated above in my
AgeInYears

Your AgeInYears was calculated using DateAppt, being the initial
appointment date. Do you want to know 'how many were 15 years or
younger when they had their initial appointment and their initial
appointment fell in 2003' (or someting similar)? Or do we now ignore
DateAppt and simply calculate their age in years in 2003?

 

[Janna]

The ages would be calculated based on their age at the time of their initial
appointment. For example. If someone's date of birth was 5/1/1987 and their
initial appointment was 4/1/2003, for the year 2003, this individual would
still be counted as 15 years old or under, because he or she hadn't turned 16
at the time of the initial appointment.

 

[peregenem]

The ages would be calculated based on their age at the time of their initial
appointment. For example. If someone's date of birth was 5/1/1987 and their
initial appointment was 4/1/2003, for the year 2003, this individual would
still be counted as 15 years old or under, because he or she hadn't turned 16
at the time of the initial appointment.

In which case, here's my attempt

CREATE TABLE tblClient (
ID INTEGER NOT NULL PRIMARY KEY,
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL,
DOB DATETIME NOT NULL,
DateAppt DATETIME NOT NULL);

INSERT INTO tblClient
VALUES (1,'Irene','Missile',#1950-01-01#,#2003-08-01#);

CREATE VIEW Clients
AS
SELECT ID, FirstName, LastName,
DOB, DateAppt,
DateDiff("yyyy", DOB, DateAppt)
+ CLNG(DATESERIAL(YEAR(DateAppt), MONTH(DOB), DAY(DOB)) > DateAppt
) AS AgeInYears
FROM tblClient;

SELECT ID,
LastName, FirstName,
IIF(AgeInYears <= 15, '15AndYounger', 'Over15')
AS age_group_name
FROM Clients
WHERE DateAppt
BETWEEN #2003-01-01#
AND #2003-12-31#;