---

MS Access 2003: Determine winning bids in a query

This MSAccess tutorial explains how to create a query to determine winning bids in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have a [lots] table and a [bids submitted] table. The [lots] table contains a number of items up for bid as well as a [qty available] field that indicates how many items are available. The [bids submitted] table contains all bids for these items.

I want to create a query that will retrieve all winning bids from the [bids submitted] table.

For example, if I have 4 novelty mugs for sale, all mugs are identical so the highest 4 bids should be shown out of the 25 bids placed.

But if I had 2 welcome mats for sale then the same query needs to return the best 2 bids for the welcome mats as well.

How can I do this?

Answer: We'll demonstrate how to do this with the example below.

If we had two tables defined as follows:

The lots table contains the details of the items for bid - including the quantity available (ie: [qty available] field), and the bids submitted table contains the bid information. The two tables are linked together based on the item_id field.

In this example, we want to return the 4 winning bids (as defined by the [qty available] field) for item_id = 1 and the 2 winning bids for item_id = 2.

We've set up the following query that uses both the IIf function and the DCount function.

The formula in the second field is set up as follows:

IIf(DCount("*","bids submitted","item_id = " & [bids submitted].[item_id] & " and bid_amount >= " & [bid_amount])<=[qty available],"Include",Null)

This will return the value "Include" if the record is a winning bid or NULL if it is not a winning bid. Then we've entered "Include" as the criteria for this calculated field - thus eliminating all losing bids from our results.

When you run this query, it returns only records from the bids submitted table for the winning bids.

The SQL for this query looks like this:

SELECT [bids submitted].*
FROM [bids submitted] INNER JOIN lots ON [bids submitted].item_id = lots.item_id
WHERE (((IIf(DCount("*","bids submitted","item_id = " & [bids submitted].[item_id] & " and bid_amount >= " & [bid_amount])<=[qty available],"Include",Null))="Include"));