Multi-tiered report

[mikem1260]

I've just been handed a report to do, and am a little rusty at Access. Basically, I need a five line report with two columns, based on the range of the second column.

I need:

1. Total value of records in field 1 where value of field 2 is 2500 or less.

2. Total value of records in field 1 where value of field 2 is between 2500 and 10000.

3. Total value of records in field 1 where value of field 2 is between 10000 and 50000.

4. Total value of records in field 1 where value of field 2 is between 50000 and 100000.

5. Total value of records in field 1 where value of field 2 is over 100000.

Again, I need this all on one report. Thanks in advance for your assistance.

 

[dhookom]

I would start by creating a table with ranges:
[tt][blue]
tblRanges
==========
RangeMin RangeMax RangeTitle
0 2500 Less then 2500
2500 10000 2500 to 10000
10000 50000 10000 to 50000
etc[/blue][/tt]

Create a query with SQL like:

SELECT RangeMin, RangeMax, RangeTitle, Sum([field 1]) as TheResult
FROM tblNoNameGiven, tblRanges
WHERE [Field 2] >[RangeMin] AND [Field 2] <=[RangeMax]
GROUP BY RangeMin, RangeMax, RangeTitle;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]