Displaying ages in ranges 3

[dday01]

I am trying to create a report which includes age (I have an age column in one of my tables), and I want to break it down into counts for five different age ranges ie. 11 and under, 12 to 20, 21 to 30, 50 and over, etc... is there a way to do this in a report/query? I am a total novice and any help would be greatly appreciated!

thanks

D

 

[BanditWk]

The best way to do this is in a Query, but then you would need to sort it (at least, that has been my issue). In the Field, type this:

AgeGroup: IIf([Age]<11,&quot;1. 11 and Under&quot;,IIf([Age] between 12 and 20,&quot;2. 12 to 20&quot;,IIf([Age] between 21 and 30, &quot;3. 21 to 30&quot;,&quot;4. 50 and Over&quot))

Sort this field in Ascending order and there ya go! You can build the report around this.

HTH Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

&quot;No need to send gifts, just send a smile.&quot;

 

[mikey69]

This is probably best done by using a Union query, which allows you to include multiple instances from an underlying table. To do this you have to write the SQL. Easiest way is to construct a simple query from the grid using &quot;Between 12 and 19&quot; or whatever and add a dummy sort field with constant value 1. Then enter the SQL view and copy. In the copied text insert the word &quot;Union&quot; before &quot;SELECT&quot; and change the criteria values in the &quot;WHERE&quot; clause and the value of the dummy sort to 2. Repeat this for all the range.

Try searching the help file for more info on Union Queries.

 

[67peshawar294]

You could also try the &quot;Partition&quot; function. I use the following for wholesale price distribution of my inventory.
You may need a numeric field associated with age, or you could add a field and place &quot;1&quot; as my [nhere] (number here)
My table is &quot;FRAMES&quot;.

SELECT DISTINCTROW Partition([whPR],0,400,Price) AS [WhPRICE Range], Sum(FRAMES.NHERE) AS NHERE
FROM FRAMES
GROUP BY Partition([whPR],0,400,Price);

NOTE:
((From SELECT... TO ...AS NHERE)) on first line.

Change the [whpr] to [age] and the &quot;0&quot; to &quot;1&quot; and the &quot;400&quot; to the maximum age, and the word price to age. When the query is run it will prompt for a number and then display the results grouped by the number entered.
jim

 

[CosmoKramer]

If you're simply looking for counts for the individual age groups, add an unbound text box for each age group and set the control source for the first one to be something like this:

=DCount(&quot;[Age]&quot;, &quot;YourTableNameHere&quot;, &quot;[Age] <= 11&quot;)

Change the criteria for the other text boxes to be the other age groups you need and you should be all set.....

 

[DrSimon]

Fascinating width of replies. I have always used Roy's method, 'cos it's simple like me. But does anyone know which of these is the most efficient?

Simon Rouse

 

[dday01]

Thank you all very much for your help, I tried both methods and both worked fine. Thanks again.

D