Age Range on chart 1

[mikelev]

I have a cluster column graph on a form. The data is from a client table that contains Gender and Age. I have not been able to produce what I need (after hours of trying) to extract the data from the table.

Could someone please explain in detail how to get a range of ages? such as 20-25, 26-35, 36-45, 46-55, 56-65, 70+, and 2 columns female and male?


Something so simple is driving me NUTS!!!


Thanks in advance

 

[domino3]

Instead of drawing the data from the basic table, could you not take it from a query, with an extra column showing age ranges. This could be iif([age] between 20 and 25, "20 - 25", iif ([age] between 26 and 30, "26 - 30" etc.

Then the age range would come from the iif column, not the actual ages.

 

[dhookom]

I would first start by creating a lookup table of age ranges. Any other method will cause possible issues in the future if you want to modify the age ranges.

tblAgeRanges
================
MinAge   MaxAge  Title
20       25      20-25
26       35      26-35
36       45      36-45
etc

You can then use this table in your query to find the correct range Title for each Age. Use this in a crosstab as the Row Heading. Set the column Heading to Gender and the value to Count of some field.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mikelev]

Thanks dhookom!!

A bit of clarification for me please...
Created the lookup table as listed above. Added the table to a new query. Question: Do I need to create a relationship between my "Client" table and the new lookup? How do i get the "Gender" and "Age" from the "Client" table to match up with my new lookup?

Im getting closer!!

 

[dhookom]

Set the criteria under your Age column to:
Between MinAge and MaxAge
Add the Title to the query and then make the query into a crosstab as I suggested.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.