Range Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a three column database; Option #, Option Price and Shares
Outstanding. I want to create a query that will summerize the number of
options outstanding by 5 price ranges; $.50 to $1.50, $1.51 to $2.50, etc.
As a newbe I can create 5 separate queries to accomplish this but there must
be an easier way.

Since the range of prices is dynamic from period to period, I would
ultimately like the query to be dynamic as well and determine the min and max
prices for 5 groupings of a roughly equal number of options. For example,
assuming the table is sorted in ascending order on price, and there are
10,000 options outstanding (variable) I would like to determine the min and
max prices for the first grouping of 2,000 options and for each grouping of
2000 options thereafter. I would then display the price range and number of
options in each range.

Any help on this would be greatly appreciated.
 
One of the less elegant, but very easy to setup solutions is to create 5
different Totals queries (1 for each price range criteria), then make a new
sixth query that has as its record source, a non-related combination of the
previous five.
-Ed
 
Stephen said:
I have a three column database; Option #, Option Price and Shares
Outstanding. I want to create a query that will summerize the number of
options outstanding by 5 price ranges; $.50 to $1.50, $1.51 to $2.50, etc.
As a newbe I can create 5 separate queries to accomplish this but there must
be an easier way.
Click to expand...

One way is to create a separate table named Ranges, with three fields - Low,
High, and Rangename: e.g.

0.00; 0.50; "Cheapskate"
0.50; 1.50; "Bargain"
1.50; 2.50; "Inexpensive"
<etc>
10.00; 100000000.00; "Luxury"

Create a Query joining your table (or query) to this query using a "Non equi
Join" -

SELECT yourtable.thisfield, yourtable.thatfield, yourtable.price,
Ranges.Rangename
FROM yourtable
INNER JOIN Ranges
ON yourtable.price >= Ranges.Low AND yourtable.price < Ranges.High;

This will give you a searchable, groupable, etc. rangename for each record.
You do need to be sure that your Low and High don't overlap or leave gaps.
Since the range of prices is dynamic from period to period, I would
ultimately like the query to be dynamic as well and determine the min and max
prices for 5 groupings of a roughly equal number of options. For example,
assuming the table is sorted in ascending order on price, and there are
10,000 options outstanding (variable) I would like to determine the min and
max prices for the first grouping of 2,000 options and for each grouping of
2000 options thereafter. I would then display the price range and number of
options in each range.
Click to expand...

Not sure I understand this. You can use a TOP VALUES query to find the min
and max price:

SELECT Min(Price), Max(Price)
FROM (SELECT TOP 2000 Price FROM yourtable ORDER BY Price DESC);

with more complex queries to get the next five groups...
 
Back
Top