Age Groups Pie Chart from Date of Birth

Can somebody show me how to create a pie chart in an access report from a date of birth field in a table?

These would be the queries (table name= tblMemReg):

SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age1
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))<16));

SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age2
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))>15 And ((Round(DateDiff("d",[DOB],Now())/365.25)))<18));

SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age3
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))>17));

Thanks
S_BurtonshawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puppydogbuddyCommented:
I would join the three queries together to create a union query as is demonstrated on the following link:

              http://www.fabalou.com/Access/Queries/unionquery.asp

and then use the union query as the source for the chart, using the access report chart wizard.  To get to the wizard, click new report in db window.
0
S_BurtonshawAuthor Commented:
Ok but I don't see how access can differentiate between the different queries once they are merged with union.

When on the the chart wizard I need to be able to select the 3 fields: Age1, Age2, Age3, so the chart can use them for each segment.
0
puppydogbuddyCommented:
You are right ..I forgot to tell you to group by age after the last query in the union.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

S_BurtonshawAuthor Commented:
Errr.. you wouldn't mind going that extra last mile on this one would you? I'm not sure how to write that.
0
puppydogbuddyCommented:
On the last line of the union query, put the following:

GROUP BY Age1,Age2, Age3
0
S_BurtonshawAuthor Commented:
Ok, this is what I have:

SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age1
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))>15 And ((Round(DateDiff("d",[DOB],Now())/365.25)))<18));
UNION ALL SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age2
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))<16));
UNION ALL SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age3
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))>17))
GROUP BY Age1,Age2, Age3;

But I get this error:

You hav etried to execute a query that does not include the specified expression '(Round(DateDiff("d",[DOB],Now())/365.25)' as part of an aggregate function.....
0
hnasrCommented:
Try using the wizard to create the pie chart:
Ex:
Table/Or query: fields v for value, l for legend
v      l
100      1
200      2
300      3
100      4

In form design, select Insert Chart using table or query:)

In your case you need a query with output that resembles the avove example.

Caution! Grouping on age for every query in the union!

Good luck
0
puppydogbuddyCommented:
My fault, have to do it like this:

GROUP BY (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age1, (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age2, (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age3
0
S_BurtonshawAuthor Commented:
'syntax error (missing operator)' error now =/

It refers to the first part of the new group by statement.
0
puppydogbuddyCommented:
Can you post the entire SQL you have?
0
S_BurtonshawAuthor Commented:
SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age1
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))>15 And ((Round(DateDiff("d",[DOB],Now())/365.25)))<18));
UNION ALL SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age2
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))<16));
UNION ALL SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age3
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DOB],Now())/365.25)))>17))
GROUP BY (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age1, (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age2,

(Round(DateDiff("d",[DOB],Now())/365.25)) AS Age3;
0
puppydogbuddyCommented:
S Burtonshaw,
Just got back on-line.  At first glance, couldn't determine what is wrong with the sql, but will take a closer look this afternoon when I get back to to the office.  Will get back to you.

                                 PDB
0
hnasrCommented:
Try:

SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age1
FROM tblMemReg
GROUP BY (Round(DateDiff("d",[DOB],Now())/365.25))
HAVING ((((Round(DateDiff("d",[DOB],Now())/365.25)))>15 And ((Round(DateDiff("d",[DOB],Now())/365.25)))<18));

UNION ALL SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age2
FROM tblMemReg
GROUP BY (Round(DateDiff("d",[DOB],Now())/365.25))
HAVING ((((Round(DateDiff("d",[DOB],Now())/365.25)))<16));

UNION ALL SELECT (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age3
FROM tblMemReg
GROUP BY (Round(DateDiff("d",[DOB],Now())/365.25)) AS Age3
HAVING ((((Round(DateDiff("d",[DOB],Now())/365.25)))>17));
0
S_BurtonshawAuthor Commented:
Ok I'm getting nowhere with this. I've redone the queries and managed to get a query with the 3 results I want by first putting these 3 select statements as individual queries:

SELECT Count(*) AS Age1
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB],Now())/365.25))<16);

SELECT Count(*) AS Age2
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB],Now())/365.25)>15) And (Round(DateDiff("d",[DOB],Now())/365.25)<18));

SELECT Count(*) AS Age3
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB],Now())/365.25))>17);

Then merging them into one query:

SELECT qryAge1.Age1, qryAge2.Age2, qryAge3.Age3
FROM qryAge1, qryAge2, qryAge3;
___________________

This outputs:

Age1 | Age2 | Age3
     3         2         7
(And I have 12 rows in the tblMemReg table)
___________________

Now if I use the chart wizard and get it to use the last merged-results query, it doesn't work.
0
puppydogbuddyCommented:
S Burtonshaw,
I apologize for not being able to get back to you yesterday, but I had a client emergency that took much longer than I anticipated.  

Based on the updated comments since I was last connected, I have the following comments:  
1. I'll leave it to your discretion, but I would substitue the Int function in place of the round function based on following reference from my code library for calculating the difference between a birthdate and now.

INT(DateDiff("d", c_BirthDate, now())/365.25)

Calculate the number of days between birth and now and divide by the exact number of days in a year, which, as you know, is 365.25 and not 365. That takes leap years into account.

The Integer function, INT( ) , truncates the result so that 25.9 becomes 25, for example; the person is 25 years old until the day she turns 26; after the age of 5, you rarely hear people say that they are 25 and a half years old.

2.  I believe the chart wizard only works with one data series at a time, and in your combined query, you've got 3 (age1, age2, age3).....so the trick is to get them in one series (i.e. Age).  I believe the following union query based on your 3 individual queries will consolidate the 3 series into one series (Age) with 3 slices (plot points) that you can label as Age1, Age2, Age3 in the legend for each slice. If Union All is duplicating data in each plot point, then just use Union without the All.  

SELECT Count(*) AS Age
FROM tblMemReg
WHERE (((Round(DateDiff("d",[DOB],Now())/365.25))<16))
Union All
SELECT Count(*) AS Age
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB],Now())/365.25)>15) And (Round(DateDiff("d",[DOB],Now())/365.25)<18));
UNION ALL
SELECT Count(*) AS Age
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB],Now())/365.25))>17);


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
Here is another go:

Table chart:
DOB
02/03/1995
04/02/1996
03/03/1950
01/01/1945
02/02/1995
03/03/2000
02/01/1960

Query: age1
SELECT "age1" AS age1, Count(*) AS countAge1
FROM chart
HAVING (((Round(DateDiff("d",[DOB],Now())/365.25))<16));

Query: age2
SELECT "age2" AS age2, Count(*) AS count_Age2
FROM chart
HAVING (((Round(DateDiff("d",[DOB],Now())/365.25))<15 And (Round(DateDiff("d",[DOB],Now())/365.25))<18));

Query:age3
SELECT "age3" AS age3, Count(*) AS count_Age3
FROM chart
HAVING (((Round(DateDiff("d",[DOB],Now())/365.25))>17));

Query: age_merged
SELECT age1.age1 As Age, age1.countAge1 As CountAge
FROM age1;
Union
SELECT age2.age2, age2.count_Age2
FROM age2;
UNION SELECT age3.age3, age3.count_Age3
FROM age3;

Then Insert chart on a form and select Query: age_merged

Good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.