Help with Age bar chart

[thkly8]

Hi i'm trying to make a bar chart report for my database but can't seem to find a good solution.



First off, i work out the age by using a query which subtracts the current year from the year they were born:
Age approx: Format(Date(),"yyyy")-[Year of Birth]

I now need to turn this into a suitable bar chart (or whatever kind of chart really), i'm thinking of grouping the ages into groups of 5 starting at 40 (since thats the lowest age) e.g. 40-44,45-59,50-54 etc.

Is this possible? The most straightforward method i can think of is to have a query with a lot of the same age fields and changing the criteria but that obviously wouldn't work. Could you be a bit detailed in your answer plez, im a bit of a noob with queries.

[June7]

Actually, you are correctly subtracting the birthdate from current date (you stated the opposite). However, that expression will result in days, not years. Days is default, use DateDiff function to specify other units. http://office.microsoft.com/en-us/ac...001228811.aspx

Now you will have to assign a group ID for the age ranges. If not too many, can be an expression in query, otherwise will need to write a function that will be called by the query.

AgeGroup: Switch(Age>=40 And Age<=44,1, Age>=45 And Age<=49,2, Age>=50 And Age<=54,3, Age>=55 And Age<=59,4, Age>59,5)

Now use this calculated group value as the series for the bar chart. If you want more descriptive group labels can use text in place of the numbers (enclose in quotation marks): 40to44, 45to49, 50to54, 55to59, >59