• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

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
0
S_Burtonshaw
Asked:
S_Burtonshaw
  • 7
  • 6
  • 3
1 Solution
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now