Counting Within Ranges

  • Thread starter Thread starter Don
  • Start date Start date
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
 
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.
 
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
 
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.
 
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
 
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...
 
Back
Top