Need Help with a query

[georgia]

I have a table that has two fields, State and Acres. I am trying to build a
cross tab query with State down the left side and buckets of acre ranges
along the top. It should look something like:


1-99 100-249 250-499
IA
IL
IN
MN

Can I run this in a single query or do I need to create three queries, one
for each acre range then combine? I would prefer to build in a single
query.

Help, been fighting this one all day.

 

[Allen Browne]

Since the ranges are arbitrary (not a fixed mulitiple of say 100 in each
one), you will need to create a table that teaches Access how to evaluate
the ranges. You can then determine which range your entries fall into, and
create a crosstab query to give you the output you want.

1. Create a table that defines the ranges. The table will have a field to
hold the minimum number of acres (perhaps named MinAcre), and it will be the
primary key. Enter records:
0
100
250
etc.

2. Build a query that returns the maximum number in the range as well. Tom
Ellison explains how to do that here:
Lookup a value in a range
at:
http://allenbrowne.com/ser-58.html

3. Combine the 2 tables, and use a crosstab query with the ranges across the
top (Column Headings), the states down the side (Row Heading), and the Value
at the intersection point.

It will take some effort to achieve.