S_Burtonshaw
asked on
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",[DO B],Now())/ 365.25)))< 16));
SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age2
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],Now())/ 365.25)))> 15 And ((Round(DateDiff("d",[DOB] ,Now())/36 5.25)))<18 ));
SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age3
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],Now())/ 365.25)))> 17));
Thanks
These would be the queries (table name= tblMemReg):
SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
Thanks
ASKER
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.
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.
You are right ..I forgot to tell you to group by age after the last query in the union.
ASKER
Errr.. you wouldn't mind going that extra last mile on this one would you? I'm not sure how to write that.
On the last line of the union query, put the following:
GROUP BY Age1,Age2, Age3
GROUP BY Age1,Age2, Age3
ASKER
Ok, this is what I have:
SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age1
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],Now())/ 365.25)))> 15 And ((Round(DateDiff("d",[DOB] ,Now())/36 5.25)))<18 ));
UNION ALL SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age2
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],Now())/ 365.25)))< 16));
UNION ALL SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age3
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],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())/36 5.25)' as part of an aggregate function.....
SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
UNION ALL SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
UNION ALL SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
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]
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
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
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
GROUP BY (Round(DateDiff("d",[DOB],
ASKER
'syntax error (missing operator)' error now =/
It refers to the first part of the new group by statement.
It refers to the first part of the new group by statement.
Can you post the entire SQL you have?
ASKER
SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age1
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],Now())/ 365.25)))> 15 And ((Round(DateDiff("d",[DOB] ,Now())/36 5.25)))<18 ));
UNION ALL SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age2
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],Now())/ 365.25)))< 16));
UNION ALL SELECT (Round(DateDiff("d",[DOB], Now())/365 .25)) AS Age3
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO B],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;
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
UNION ALL SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
UNION ALL SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
WHERE ((((Round(DateDiff("d",[DO
GROUP BY (Round(DateDiff("d",[DOB],
(Round(DateDiff("d",[DOB],
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
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
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",[DO B],Now())/ 365.25)))> 15 And ((Round(DateDiff("d",[DOB] ,Now())/36 5.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",[DO B],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",[DO B],Now())/ 365.25)))> 17));
SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
GROUP BY (Round(DateDiff("d",[DOB],
HAVING ((((Round(DateDiff("d",[DO
UNION ALL SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
GROUP BY (Round(DateDiff("d",[DOB],
HAVING ((((Round(DateDiff("d",[DO
UNION ALL SELECT (Round(DateDiff("d",[DOB],
FROM tblMemReg
GROUP BY (Round(DateDiff("d",[DOB],
HAVING ((((Round(DateDiff("d",[DO
ASKER
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())/36 5.25))<16) ;
SELECT Count(*) AS Age2
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB] ,Now())/36 5.25)>15) And (Round(DateDiff("d",[DOB], Now())/365 .25)<18));
SELECT Count(*) AS Age3
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB] ,Now())/36 5.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.
SELECT Count(*) AS Age1
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB]
SELECT Count(*) AS Age2
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB]
SELECT Count(*) AS Age3
FROM tblMemReg
WHERE ((Round(DateDiff("d",[DOB]
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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())/3 65.25))<16 ));
Query: age2
SELECT "age2" AS age2, Count(*) AS count_Age2
FROM chart
HAVING (((Round(DateDiff("d",[DOB ],Now())/3 65.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())/3 65.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!
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
Query: age2
SELECT "age2" AS age2, Count(*) AS count_Age2
FROM chart
HAVING (((Round(DateDiff("d",[DOB
Query:age3
SELECT "age3" AS age3, Count(*) AS count_Age3
FROM chart
HAVING (((Round(DateDiff("d",[DOB
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!
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.