I am trying to update 4 fields in a record from data in another table. The database has to do with auctions and the bidding process. I want to be able to delete a high bid in an auctions table and replace it with the next higher bid. The main Auction table has auction items along with the highest bid amount and the bidder number. The Bid table has appended values of each bid so there is a constant update of the bid process. So far, for Item X, I delete the existing high bid data in the auction table and bid table because that someone doesn't want to pay for the items. I also have a simple query that shows the next higher bid using the MAX function, which, after deleting the former high bid, is now the new high bid.
My problem is the next step, to add the new high bid data from the bid table to the auctions table record. In the bid table, there are multiple bidders for the same item. I thought if the item and bid amount were the highest, then I could just use that new "high bid"number (from the query) as a "criteria" in my update query (I tried "Max([query name]![results])") and that didn't work. I've also tried various "fields" by adding the "Max" query to the query's table area. So far all I get are error messages or the lower bid (whether the field is sorted or not.)
I would appreciate any ideas that might make a query work as criteria or some other action that might make a working query.