Custom Group in Reports

[Karen831]

Hello -

I have an Access report based on one table with three fields:

"# of Years", "# of people at school", "# of people applied"

data looks like this:

0 500 35
1 530 60
2 560 60
3 400 50
4 430 80
5 500 44
6 500 46
7 500 65
8 600 62
9 650 52
10 700 55

I need 0 thru 6 to show as is, and group 7+ years all together in one and sum all numbers above. So it should look like this:

0 500 35
1 530 60
2 560 60
3 400 50
4 430 80
5 500 44
6 500 46
7 + 2450 234

Can anyone help me figure out how to do this. I am using Access 2003.

Thanks

 

[Michael J Ross]

One way i can think of is to use a subreport for the 7+.

Create a query for the 0 -6 and then another query for the summary 7+, base the main report on the first query and then add the subreport to the report.

 

[stopher]

Another way is to create an aggregate query with a "decode" the # of years by entering the following formula in the query:
AgeGroup: IIf([# of years]>6,"7+",CStr([# of years]))

Then your query will be:

SELECT IIf([# of years]>6,"7+",CStr([# of years])) AS AgeGroup, Sum([# of people at school]) AS [TotPeopleAtSchool], Sum([# of people applied]) AS [TotPeopleApplied]
FROM myTable
GROUP BY IIf([# of years]>6,"7+",CStr([# of years]))

You can then base your report on the query. I've assumed that your "# of years" is a number format.

By the way, characters such as # and space should be avoided in field names as they can cause problems later on.

hth
Stopher

 

[Karen831]

Groups in Reports

Thank you both!

I think the If statement will work better for me in this instance though because I then need to base my chart on this table as well.

Stopher - When you say enter this code in; I'm not code writing at this point, where should I do this exactly in the query?

Thank you. Thank you.

 

[stopher]

Thank you both!

I think the If statement will work better for me in this instance though because I then need to base my chart on this table as well.

Stopher - When you say enter this code in; I'm not code writing at this point, where should I do this exactly in the query?

Thank you. Thank you.

The "code" is actually SQL which is the script used by database engines to define and run queries. When you create a query in the Access design view, the designer creates the SQL for you. However, you can copy/paste the SQL I gave by creating a new query in the query design view, then click View->SQL View.

Make sure you have changed it to reflect your table name (i.e. change myTable) and also check I've used the correct field names.

After you have pasted the SQL you can run it or switch back to design view to see how it looks in design view.

hth
Stopher

 

[Karen831]

Group in Reports

Thank you both. The "IF" statement worked in the query.


Appreciate your help

Karen