Marking the Highest Record in a Group

[exm206]

We recently created a database for an auction we ran. Each item in the auction was given a Lot #. Each person who bid on at least one item was given a Bidder #. In our database we have a table with each bidders' information, including which lots he bid on and for how much. We also have a table for each Lot, this table shows all the bidders who bid on this lot and for how much. So for example, for Lot #1, there might be three bidders. Lets say Bidder #1 bid $100, Bidder #2 bid $200, and Bidder #3 bid $300. What we want to do is hopefully run some sort of query that will go through each Lot # and in some way "mark" the highest bid. So in this example, Bidder #3 would be marked as the highest. I imagine we will have to create a check box field and do some sort of update query on the check box field, but I'm not sure exactly how to go about it. Any help that you could give me would be greatly appreciated. Thank you.

 

[Dugantrain]

You should be able to write a query off of the Lots table which will Group By Lot, Group By Bidder, Max of Bid.
This will display each Lot, the Max Bid, and the Bidder who made that bid.
*Note* I just realized that "Bid" is one of those words that starts to look and sound weird when you say it or write it too much!

 

[jobb]

try this:

Make both Lot and Bid Amount the primary key in the Lot table (include an ID number in your primary key if there are two bids for the same lot with the same amount). You can add a checkbox field to your Lot table for [highestbid]. Then save a query to Group By Lot and Max Bid Amount.

Then make an update query that joins the query above to your Lot table, joining both Bid Amount and Lot, and include your [highestbid] field from your Lot table. Update [highestbid] to Yes.

I think that should work..

 

[jobb]

my mistake... make the first query a Make-Table Query and query that table against your Lot table like I described above. This process would probably be much easier if you wrote it into a macro. There could be a much easier way, but I don't know of any.