Assigning a banding marker in a query

[JSFEcho]

I have a table that has a numeric column "sales" that ranges from 1 to
1,000,000. In a query, I am attempting to assign a banding marker to each
record. I have 200 bands so an IIf statement won't work.

So for instance, one of the 200 bands that is >=22 and less than 29 called
"Quite Low". How can I append the "Quite Low" marker to a new column,
"band", in my query for records that fit the criteria.

In the end, each row in the query should have a value for a similarly
defined range in the "Band" column.

Thanx - John

 

[John Spencer]

Build a table of Bands
BandName : text field with name of band
StartRange : Lowest value in the band
EndRange : First Value greater than the end of the band range

Don't overlap. You might have records like
Band : StartRange : EndRange
Low : 1 : 21
Medium : 21 :50
High : 50 : 20000

Join that table to your Sales table with a non-equi join

SELECT Sales.*, Bands.Band
FROM Sales INNER JOIN Bands
ON Sales.SaleAmount >= Bands.StartRange
AND Sales.SaleAmount < Bands.EndRange



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I have a table that has a numeric column "sales" that ranges from 1 to
1,000,000. In a query, I am attempting to assign a banding marker to each
record. I have 200 bands so an IIf statement won't work.

So for instance, one of the 200 bands that is >=22 and less than 29 called
"Quite Low". How can I append the "Quite Low" marker to a new column,
"band", in my query for records that fit the criteria.

In the end, each row in the query should have a value for a similarly
defined range in the "Band" column.

Thanx - John

A "Non Equi Join" query is the ticket for this. Create a table Bands with
three fields, Low, High, and Band: values 22, 29, "Quite Low".

Then create a Query joining your table to Bands like

SELECT <whatever>, Bands.Band
FROM yourtable
INNER JOIN Bands
ON yourtable.Sales >= Bands.Low AND yourtable.sales < Bands.High

You'll need an "infinity" value for the highest band, 1000001 or 2,000,000,000
for example; and this isn't quite kosher, because the Low and High values are
interdependent from record to record; you need to be sure they don't overlap
and don't have gaps.

 

[Marshall Barton]

I have a table that has a numeric column "sales" that ranges from 1 to
1,000,000. In a query, I am attempting to assign a banding marker to each
record. I have 200 bands so an IIf statement won't work.

So for instance, one of the 200 bands that is >=22 and less than 29 called
"Quite Low". How can I append the "Quite Low" marker to a new column,
"band", in my query for records that fit the criteria.

In the end, each row in the query should have a value for a similarly
defined range in the "Band" column.

I think the only reasonable way to approach this is to
create a table that defines the bands.

table Bands:
Low Long Primary Key 'lower bound of band
Descr Text 'description of band

The records could then liik like:
0 Really Low
22 Quite Low
29 Kind Of Low
. . .

With that in place, your query could look like:

SELECT T.Sales, T.f1, T.f2. . . .
(SELECT TOP 1 B.Descr
FROM Bands As B
WHERE T.Sales > B.Low
ORDER BY B.Low DESC) As Band
FROM yourtable As T

 

[JSFEcho]

Thanx for the ideas. I'll give them a shot.

John