Update Query: Modifying Data Using Top Values

[William McKinley]

Background: I am trying to set up a quality program in my facility to monitor an individual's inventory putaway accuracy. Every day, thousands of products are received from our suppliers and put away into storage in our warehouse. I have records for each of these transactions. To ensure accuracy, I would like to begin having a person going behind these putaways and verifying that they are correct. Since there are so many transactions per day and only one employee to verify them, I'll only be able to select a small sample population to analyze. This part of the project only deals with how to go about differentiating the records we have selected for verification from the total record pool.



Ideally, I would like to create a report that lists X number of putaway transactions (lets say 50). Once the report has been run, I would like to update the table containing putaway transaction data to show that these records have been verified/printed. If the employee completes this list of 50, I would like to rerun the report for a new set of 50 unprinted/unverified records, update those 50 records to show that they have been printed/verified, allow the employee to complete the verification process, and continue this process infinitely.

Does anyone know a good way to approach this? I tried to create a report along with a similarly structured UPDATE query/SELECT TOP 50 subquery. I never got it to work and kept getting an error saying something like "At most this subquery must return one record." I've attached a sample of my table and it's data for reference. If any additional information is required, don't hesitate to ask.

[William McKinley]

Solved using this update query/subquery.

UPDATE [Receiving Transactions] SET Verified = "YES"
WHERE HistorySequence IN (SELECT TOP 5 [Receiving Transactions].HistorySequence
FROM [Receiving Transactions]
WHERE TeamName = "XXX" AND PriorOnHand = 0 AND Verified Is Null ORDER BY HistorySequence DESC);

Also, I attached this query to an On Click event procedure for a command button on the report. Works like a charm!