a top n query where the n is field in another table?

[Howard]

a problem to do with bidding on a limited number of items.
(simplified form)
table1 contains housetype and NumAvailable e.g.
type numAvail
A 7
B 2
C 5

table2 contains bids on buying a type of house with housetype, bidPrice,
customerID e.g.
type bid id
B 500 1
C 700 2
A 400 2
B 800 3
B 900 5
C 700 4
B 300 6

a customer (ID) may make many bids on one or more house types

To see if their bid is bigger than the smallest of the existing bids on
that house type I want to do a query something like SELECT TOP n from
table2 where type = mytype but where the 'n' is taken from table1 i.e.
if there are 2 houses of that type I want the top 2 bids if there are 7
of that type I want the top 7 bids.

I then do a max and min group to see if a new bid is bigger than any of
the 'n' existing bids on that type

How can something like this be done?

Howard

 

[KARL DEWEY]

Can not be done with TOP n but you can get there using ranking and set
criteria on rank from the table.

 

[Howard]

Can not be done with TOP n but you can get there using ranking and set
criteria on rank from the table.

please could you explain a little more how this is done. I haven't uses
ranking before
Howard

 

[John Spencer]

This can be solved using queries like the following. Unfortunately you cannot
build these queries using the query design tool, but must use the SQL View.

Ranking query
SELECT A.Type, A.Bid, A.ID, 1 + Count(B.Bid) as Rank
FROM Table2 as A LEFT JOIN Table2 as B
ON A.Type = B.Type
AND A.Bid > B.Bid
GROUP BY A.Type, A.Bid, A.ID

Now join that query to Table1
SELECT C.*
FROM Table1 as C INNER JOIN RankingQuery as Q
ON C.Type = Q.Type
And C.NumAvail > Q.Rank

IF you must do this in query design view, you can try
Ranking query.
-- add table2 to the query two times
-- join the type field to the type field
-- add the fields from one copy of the table to the list
-- add the Bid field a second time
-- Select View: Totals
-- Change GROUP BY To WHERE under the second bid field
-- Enter criteria under the where

[TableName_1].[Bid]

-- Add the BId field from the second table
-- Change GROUP BY to Count
(That should return 0 to N as the ranking)

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