Group by one field, but not another!?

[Demonweare]

To lay it plainly, I am designing an Auction database.



I have a table that lists each bid as they are placed. Fields: Artwork No., Bidder No., Amount

I need to then be able to see, at any time, which bidders have the highest bid on each artwork. Output: Artwork No., Bidder No., Amount (max of...)

Building a query that groups on the Artwork No. and finds the max of the Amount does not work because I don't know which bidder had the highest bid. Adding the Bidder No. field produces more problems.

Is there a way that I can design my query so that I know what the highest bid on each Artwork is and who had that highest bid, or will I have to build a macro?

Thanks, guys!

[evander]

Yes there is. You'll have to create intermediate queries.
Check out the attached file.

But it won't work if there are ties. I guess you would choose the person who made an earlier bid in case of ties.

[NoellaG]

Hi,

have you tried using a dlookup function with a nested dmax?
Something like
DLookup("BidderNo","MyBidsTable", "Article = " & articlenr & " and Amount = " & dmax("Amount","MyBidsTabel", "Article = " & articlenr ))

Could be rather slow, though.