Results 1 to 6 of 6
  1. #1
    Richard-S is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2016
    Location
    Chula Vista, California, United States
    Posts
    22

    How to create a query to update a record based on another query's results as a criteria?

    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.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Have you tried creating a query that only returns the results that you would like to delete from your table? If you create a Query Object that retrieves the data you want to change, you can use the name of that query in an UPDATE query.

    I would probably just add a column to the table that describes the bid. For instance add an attribute (column) for whether a bid is "ActiveBid". Give it a data type of yes/no and have it default to Yes.

    Then, when a bidder decides he was only kidding about actually bidding, change that record from ActiveBid = Yes to ActiveBid = No.

  3. #3
    Richard-S is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2016
    Location
    Chula Vista, California, United States
    Posts
    22
    ItsMe: Thanks for your reply. I am going to add that idea to the bidder's form so bidders will have more control over their bids before closing out. Sorry, I left out that this Update is done after the bidding is completed and people are lining up to pay for their successful bid.

    Deleting a bid is only done when the bidders have come to the table where payment is made for the items. At that time, a bidder will say it is too expensive and the person taking money will (hopefully) be able to delete that high bid and replace it with the next person who bid (I'm trying to automate the process). Bidders cannot change their bids once the auction is done, which is the group's policy. Only the pay person can delete a bid. My problem is getting that next lower bid into the auction items table to replace the former bid.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Yeah, so it can be called ActiveBid, DeadbeatBidder, ColdFeet, etc.. The principal is the same. Create a Boolean and include this value in your query's criteria. It can be used during the bidding process and it could be used after the bidding is closed. Or, you can have a different Boolean for different circumstances.

    WHERE ActiveBid = True AND DeadbeatBidder = False

  5. #5
    Richard-S is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2016
    Location
    Chula Vista, California, United States
    Posts
    22
    ItsMe: Thanks, I finally got this working as you said. The extra field did it.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Glad to hear things are moving forward with your project. I rarely allow for deleting records in any of the tables.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-23-2016, 04:36 PM
  2. Replies: 3
    Last Post: 02-22-2016, 03:38 PM
  3. Query results based on checked record.
    By jtm013 in forum Queries
    Replies: 3
    Last Post: 08-14-2014, 10:43 AM
  4. Create Message Box based on query results
    By stanley721 in forum Queries
    Replies: 7
    Last Post: 02-14-2013, 10:04 AM
  5. Replies: 1
    Last Post: 10-29-2012, 08:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums