Calculate Tie on Top 1 record query

[sacsadmin]

OK I have looked in all the threads for the past month and did not find a question similar to this one. I will try to keep it simple.

I have composed an Access Database that mimics a silent auction. The clients bids are timestamped as they are placed in the database. The timestamp is output on a report. These are currently sorted by hand to determine the winner if there is a duplicate bid on an item.

I have a query that returns the top record (highest bid) for each item, however sometimes clients bid the same winning amount.

Question: How do I get the query to look at the ties and pick the earliest one as the winner and return it as the winning bid. Keep in mind the query also has to work if there is not a tie and only return the top record as well.

As always thanks in advance for your help.

 

[GHolden]

Hi

Assuming the table was called tblBids, with fields fldTime and fldBid

SELECT TOP 1 tblBids.fldTime , tblBids.fldBid
FROM tblBids
ORDER BY tblBids.fldTime , tblBids.fldBid DESC;

Would return the higest bid with the lowest (earliest) time.

Hope this helps.

There are two ways to write error-free programs; only the third one works.