Find a single row and update it with nested queries

Question

Good evening everyone, I'm trying to do an update on a Table but I can't really make it work The feature needed is: -Watch a field on a form, it contains the number of people that need to sit at the restaurant table. -Find the first free table that has enough seats, set it as busy and assign a random waiter

Any idea?

more db infos: Table "Waiters" is composed by ID(Autonumber),Name(Short Text). Has 2 names atm Table "Tables" is composed by ID(Autonumber),Seats(Number),Busy(y/n),Waiter(short text). All tables have a fixed number of seats and have no Waiter + not busy

SOLUTION:

In the end i used "First" for the assignment and it works perfectly as it follows:

UPDATE Tables SET Tables.Waiter = DLookUp("FirstName","TopWtr")
WHERE ID IN (SELECT FIRST (ID)
FROM Tables
WHERE Seats >= Val(Forms!Room!Text12) AND Waiter Is Null);

Top wasn't working because it was returning multiple records - every table with same number of seats - and couldn't make it work with DISTINCT. This works probably because the table is already ordered by seats

Thanks to June7 for the input

Answer 1

Cannot SET a field value to result of a SELECT subquery - SELECT returns a dataset not a single value. Can return a single value with domain aggregate function.

Build a query object named TopWtr: SELECT Top 1 ID FROM Waiters ORDER BY Rnd(ID);

Then use DLookup to pull that value. The Busy field seems redundant because if table has a waiter assigned that would indicate busy.

UPDATE Tables SET Tables.Waiter = DLookUp("ID","TopWtr"), Tables.Busy = True 
WHERE ID IN (SELECT TOP 1 ID FROM Tables 
             WHERE Seats >= Val(Forms!Room!Testo17) AND Waiter Is Null 
             ORDER BY Seats)

An INNER JOIN may be preferable to WHERE clause:

UPDATE Tables INNER JOIN (SELECT TOP 1 ID FROM Tables 
             WHERE Seats >= Val(Forms!Room!Testo17) AND Waiter Is Null 
             ORDER BY Seats) AS T1
ON Tables.ID = T1.ID
SET Tables.Waiter = DLookUp("ID","TopWtr"), Tables.Busy = True