Counting Within Ranges

D

Don

Is there a straight forward way to count (total) the number of items in a
range? For example, if I have a table with part numbers and prices, is
there a way to design a query to tell me the number of parts <$5.00,
$5.01-10.00, $10.01-$20.00, etc, etc, etc? Obviously, the actual prices are
random and vary within the ranges.

Any advice or pointers to references will be greatly appreciated!

Thanks!

Don
 
D

Duane Hookom

I recommend creating a table of price ranges:
tblPriceRanges
==================
MinPrice Currency
MaxPrice Currency
RangeTitle Text

You can then create a query with this table and your other table. Set the
criteria under your Prices field to
Between MinPrice And MaxPrice
View totals and Group By RangeTitle and Count Price.
 
D

Don

Duane,

Okay, I created a couple of small sample tables to test this.
Unfortunately, I am a bit confused about the query. The first column of the
query view is the Price field. Under it I selected "Count" under total,
"Show" and for criteria used 'Between [minPrice] and [maxPrice]'. The
second column is the RangeText field and selected "GroupBy" and "Show".
Pretty much what you proposed. What is confusing me is your comment "View
totals and ..."

Fundamentally I see what you are getting at. Just not skilled enough to
finish it.

Thanks for the help!!

Don
 
K

KARL DEWEY

Try this. Create a totals query with the folowing fields --
Price Range: Partition([Price],0,999999,10)
Quanity in Price Range: Price
Set the Totals part of the grid to Group By for the Price Range: column and
Count for the Quanity in Price Range: column.
 
D

Don

Duane,

After some additional tinkering, got it to work! (I ended trying on a
different app (time based).)

SQL is as follows:

SELECT Count(tblTimeValues.ArrivalTime) AS CountOfArrivalTime,
tblTimeRanges.RangeTitle, tblTimeRanges.minTime, tblTimeRanges.maxTime
FROM tblTimeRanges, tblTimeValues
WHERE (((tblTimeValues.ArrivalTime) Between [mintime] And [maxtime]))
GROUP BY tblTimeRanges.RangeTitle, tblTimeRanges.minTime,
tblTimeRanges.maxTime
ORDER BY tblTimeRanges.minTime;

Thanks for getting me pointed in the right direction!

Don
 
D

Don

Karl,

Since PARTITION works on integers, I went back and tinkered with Duane's
suggested approach and managed to get it working:

SELECT Count(tblTimeValues.ArrivalTime) AS CountOfArrivalTime,
tblTimeRanges.RangeTitle, tblTimeRanges.minTime, tblTimeRanges.maxTime
FROM tblTimeRanges, tblTimeValues
WHERE (((tblTimeValues.ArrivalTime) Between [mintime] And [maxtime]))
GROUP BY tblTimeRanges.RangeTitle, tblTimeRanges.minTime,
tblTimeRanges.maxTime
ORDER BY tblTimeRanges.minTime;


Thanks for your suggestion! Probably end up using PARTITION for something!

Don




KARL DEWEY said:
Try this. Create a totals query with the folowing fields --
Price Range: Partition([Price],0,999999,10)
Quanity in Price Range: Price
Set the Totals part of the grid to Group By for the Price Range: column
and
Count for the Quanity in Price Range: column.

Don said:
Is there a straight forward way to count (total) the number of items in a
range? For example, if I have a table with part numbers and prices, is
there a way to design a query to tell me the number of parts <$5.00,
$5.01-10.00, $10.01-$20.00, etc, etc, etc? Obviously, the actual prices
are
random and vary within the ranges.

Any advice or pointers to references will be greatly appreciated!

Thanks!

Don
Click to expand...
Click to expand...
 
Top