custom intervals in reports

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

janaki - 22 Sep 2005 23:15 GMT

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

Reply to this Message

Marshall Barton - 23 Sep 2005 00:17 GMT

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)

>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
[quoted text clipped - 33 lines]
> 20
>etc

Signature

Marsh
MVP [MS Access]

Reply to this Message

janaki - 27 Sep 2005 23:15 GMT

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

>70years

Reply to this Message

Marshall Barton - 28 Sep 2005 00:09 GMT

>This was excellent!!! Thank you so much!!!
>
[quoted text clipped - 13 lines]
>60 69years
>>70years

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

janaki - 28 Sep 2005 16:09 GMT

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);

> Then in your report's record source query, Join the
> AgeGroups table using:
[quoted text clipped - 8 lines]
> 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.

Reply to this Message

Marshall Barton - 03 Oct 2005 15:46 GMT

>Thanks so much for the help.........clarification on the SELECT syntax:
>getting the error message "Between operator without And in query expression
[quoted text clipped - 4 lines]
>FROM table AS T LEFT JOIN AgeGroups AS A ON (T.age>=A.Low) AND
>(T.Age<=A.High);

Interesting that there's a version of the query parser(?)
that doesn't recognize Between, but It's good to hear that
you found an equivalent expression that works.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Duane Hookom - 28 Sep 2005 00:13 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

> Marsh,
> This was excellent!!! Thank you so much!!!
[quoted text clipped - 14 lines]
> 60 - 69years
>>70years

Reply to this Message