Counting records in multiple defined ranges

[RichF01]

I have a large database with two relevant fields that I am attempting to extract and summarise.

eg
Area Volume
SW 10000
NE 8000
NW 7500
SE 12000
SW 9000
SW 8000

I would like to summarise the data in an array, with Area in rows and Volume in Columns. The Volume is to be grouped in ranges (eg 8000<Volume<9000) I want to record the number of records within an Area and Volume range with the count function eg
<=8000 8001<10000 >=10001
NW 1
NE 1
SW 0 2 1
SE 1

I have found a way running a query for each Volume range, and then a Query on the Queries by Area, however this is v tedious and not efficient

I am new to SQL/Access and have no knowledge of syntax etc. I would have prefered to do this in Excel, but volume of data being used means this is not practical. I am thinking of some form of do loop/if statement

Any advice very welcome
Thanks in advance
R

 

[PHV]

Something like this (in the SQL view of the query window)?
SELECT Area, Sum(IIf([Volume]<=8000),1,0)) AS [<=8000], Sum(IIf([Volume]>8000 AND [Volume]<10000),1,0)) AS [8001<10000], Sum(IIf([Volume]>=10000),1,0)) AS [>=10000]
FROM yourTable
GROUP BY Area;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RichF01]

After fiddling with brackets works like a dream. Thanks a lot.
Rich

 

[dhookom]

I would create a table of ranges with min and max values as well as a "title" for the range. A crosstab could then be used to create your result.

This would be more flexible because you could change the values in the table when the ranges change. You would not have to go back into the design view of a query and change expressions.

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]