analysising ages in database.

[Andre C]

I have a list of clients all with dates of birth.

I can create a querry that will calculate their exact ages fine but I
want to analysis this in a chart report. The type where age is the X
axis and frequency is the Y access.

I assume there must be some way of creating a querry to count the
number of clients per year of age. But how?

ACC

 

[Douglas J. Steele]

The SQL for your query should look like:

SELECT DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format( [DOB], "mmdd"), 1, 0) AS Age, Count(*)
FROM MyTable
GROUP BY DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format( [DOB], "mmdd"), 1, 0)

 

[Andre C]

The SQL for your query should look like:

SELECT DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format( [DOB], "mmdd"), 1, 0) AS Age, Count(*)
FROM MyTable
GROUP BY DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format( [DOB], "mmdd"), 1, 0)

I tried to convert this thus

SELECT Contacts.Birthdate, Contacts.Active, Contacts.ICSNumberID,
(Date()-([Birthdate]))/365.25 AS Age,
Count(DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0))
AS Age2, Count(*)
FROM Contacts
GROUP BY Contacts.Birthdate, Contacts.Active, Contacts.ICSNumberID,
(Date()-([Birthdate]))/365.25, Count(*)
HAVING (((Contacts.Active)="Y"));

but it complained that "cannot have aggrgated funcitonin group by
count(*)

 

[Douglas J. Steele]

You can't include Count(*) in the GROUP BY clause.

Birthdate should not be in the SELECT statement: you won't get any kind of
summary if you have it. As well, what's Contacts.ICSNumberID? If it's unique
for each person, you can't include it if you want a summary.

The formula I showed in my example for calculating the age should not be put
in the Count function: it should replace your formula (as
(Date()-([Birthdate]))/365.25 isn't a particularly accurate way to calculate
age)

All you need is

SELECT
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*)
FROM Contacts
GROUP BY
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
HAVING (((Contacts.Active)="Y"));

If ICSNumberID makes sense to have in the summary, then you'd have

SELECT ICSNumberID,
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*)
FROM Contacts
GROUP BY ICSNumberID,
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
HAVING (((Contacts.Active)="Y"));

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The SQL for your query should look like:

SELECT DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format( [DOB], "mmdd"), 1, 0) AS Age, Count(*)
FROM MyTable
GROUP BY DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format( [DOB], "mmdd"), 1, 0)

I tried to convert this thus

SELECT Contacts.Birthdate, Contacts.Active, Contacts.ICSNumberID,
(Date()-([Birthdate]))/365.25 AS Age,
Count(DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0))
AS Age2, Count(*)
FROM Contacts
GROUP BY Contacts.Birthdate, Contacts.Active, Contacts.ICSNumberID,
(Date()-([Birthdate]))/365.25, Count(*)
HAVING (((Contacts.Active)="Y"));

but it complained that "cannot have aggrgated funcitonin group by
count(*)

 

[Andre C]

On Sun, 4 Sep 2005 18:18:46 -0400, "Douglas J. Steele"


Sorry this is not working and the SQL is a little too advanced for me.
I directly cut and paste what you had below (excluding ICS which is
unique per record) and I get the folowing error message

"You tried to execute a querry that does not include the specified
expression 'Contacts.Active="Y" as part of an aggregate function."

I need the field contact.active in as you have deduced as the table
contacts can contain inactive records whihc must be retained, not
deleted. Hence I need to filter on active=Y

ACC

 

[Andre C]

On Sun, 4 Sep 2005 18:18:46 -0400, "Douglas J. Steele"
I note that taking out contact.active gives me closer to what I want
but it includes the non-active clients.

 

[Douglas J. Steele]

Sorry: my error. Even though Contacts.Active isn't in the list of fields to
be retrieved, since it's referenced in the SQL, it still needs to be
included in the GROUP BY clause.

SELECT
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*)
FROM Contacts
GROUP BY
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0),
Active
HAVING Active="Y"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

On Sun, 4 Sep 2005 18:18:46 -0400, "Douglas J. Steele"


Sorry this is not working and the SQL is a little too advanced for me.
I directly cut and paste what you had below (excluding ICS which is
unique per record) and I get the folowing error message

"You tried to execute a querry that does not include the specified
expression 'Contacts.Active="Y" as part of an aggregate function."

I need the field contact.active in as you have deduced as the table
contacts can contain inactive records whihc must be retained, not
deleted. Hence I need to filter on active=Y

ACC

You can't include Count(*) in the GROUP BY clause.

Birthdate should not be in the SELECT statement: you won't get any kind of
summary if you have it. As well, what's Contacts.ICSNumberID? If it's
unique
for each person, you can't include it if you want a summary.

The formula I showed in my example for calculating the age should not be
put
in the Count function: it should replace your formula (as
(Date()-([Birthdate]))/365.25 isn't a particularly accurate way to
calculate
age)

All you need is

SELECT
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*)
FROM Contacts
GROUP BY
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
HAVING (((Contacts.Active)="Y"));

If ICSNumberID makes sense to have in the summary, then you'd have

SELECT ICSNumberID,
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*)
FROM Contacts
GROUP BY ICSNumberID,
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
HAVING (((Contacts.Active)="Y"));

 

[Andre C]

Thanks, that's done the job

 

[John Vinson]

Sorry: my error. Even though Contacts.Active isn't in the list of fields to
be retrieved, since it's referenced in the SQL, it still needs to be
included in the GROUP BY clause.

SELECT
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*)
FROM Contacts
GROUP BY
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0),
Active
HAVING Active="Y"

Might it be better to change the HAVING to WHERE, in order to do the
filtering BEFORE the group by, and get rid of the need to group by
Active at all?

John W. Vinson[MVP]

 

[Douglas J. Steele]

Sorry: my error. Even though Contacts.Active isn't in the list of fields
to
be retrieved, since it's referenced in the SQL, it still needs to be
included in the GROUP BY clause.

SELECT
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*)
FROM Contacts
GROUP BY
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0),
Active
HAVING Active="Y"

Might it be better to change the HAVING to WHERE, in order to do the
filtering BEFORE the group by, and get rid of the need to group by
Active at all?

As usual, you're right. I was too lazy to test before replying! <g>

 

[Andre C]

What does that mean? I have never seen the HAVING before anyway.

 

[Duane Hookom]

John was suggesting that this might be a better SQL syntax for your query:

SELECT
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)
AS Age, Count(*) As NumOf
FROM Contacts
WHERE Active="Y"
GROUP BY
DateDiff("yyyy",[Birthdate],Date())-IIf(Format(Date(),"mmdd")<Format([Birthdate],"mmdd"),1,0)