custom intervals in reports

[janaki]

I am trying to figure out how I can generate a report which counts records
grouped in intervals. the intervals are grouping by Age, in five year
increments, and separated by Male/Female. the report works fine (I have the
Age groups nested in the Gender groups), with the correct total counts, but I
can't figure out how to label which intervals the counts represent. To make
this clearer, I'd like the report to look like this:

Female:
Ages Count
0-4 3
5-9 6
10-14 4
15-20 10
etc
Male:
Ages Count
0-4 6
5-9 2
10-14 7
15-20 20
etc

What I am getting now is:
Female:
Ages Count
3
6
4
10
etc
Male:
Ages Count
6
2
7
20
etc

any thoughts?
thanks,
janaki

 

[Marshall Barton]

With nice, uniform intervals like that, you should look into
using the Partition function instead of trying to use the
Group Interval property.

Set the grouping expression to:
=Val(Partition(Age, 0, 100, 5))

and your Ages text box would use the expression:
=Partition(Age, 0, 100, 5)

 

[janaki]

Marsh,
This was excellent!!! Thank you so much!!!

Now, your answer begs the question, how to do this with uneven intervals?

Ideally, i'd like to pull data on the following intervals:
0 – 2 years
3 – 5 years
6 – 10 years
11 – 15years
16 – 20years
21 – 25years
26 – 29years
30 – 39years
40 – 49years
50 – 59years
60 – 69years

 

[Marshall Barton]

This was excellent!!! Thank you so much!!!

Now, your answer begs the question, how to do this with uneven intervals?

Ideally, i'd like to pull data on the following intervals:
0 – 2 years
3 – 5 years
6 – 10 years
11 – 15years
16 – 20years
21 – 25years
26 – 29years
30 – 39years
40 – 49years
50 – 59years
60 – 69years

Write you own function to return the age bracket or create a
table to define the brackets. Since I don't want to have to
edit code when some executive mind/mood changes, I woud use
the table approach:

table AgeGroups:
Bracket Text PK
Low Integer
High Integer

Populate the table's records:
0-2 0 2
3-5 3 5
. . .

Then in your report's record source query, Join the
AgeGroups table using:

SELECT T.lastname, T.gender, T.age, A.Bracket, . . .
FROM table As T LEFT JOIN AgeGroups As A
ON T.age Between A.Low And A.High

The report can then group on the expression Val(Bracket) and
the text box would just be bound to the Bracket field.

Note that you can not specify that kind of Join in the query
design grid, so you have to edit the query in SQL view.

 

[Duane Hookom]

You begin by assuming the intervals will change over time. Perhaps the
quickest method is to create a function in a general module named
"modBusinessCalcs"

Public Function GetAgeInterval(pintAge as Integer) as String
Select Case pintAge
Case is < 3
GetAgeInterval = "0 - 2 years"
Case 3 to 5
GetAgeInterval = "3 - 5 years"
Case 6 to 10
GetAgeInterval = "6 - 10 years"
'... etc...
End Select
End Function

Then you can use this function in queries, control sources, code,...
AgeInterval: GetAgeInterval([Age])

If you want to allow users to modify the age intervals, you need to create a
table that allows for a lookup.

 

[janaki]

Thanks so much for the help.........clarification on the SELECT syntax:
getting the error message "Between operator without And in query expression
'T.age Between A.Low'.

but this works:
SELECT T.gender, T.age, A.Bracket
FROM table AS T LEFT JOIN AgeGroups AS A ON (T.age>=A.Low) AND
(T.Age<=A.High);