How do you search for "non-bookings" ? Help please!

[seannypoos]

Quite new to Access so hope this makes sense -
I have a restaurant table booking system. Works well but ideally what I want to do is search for tables that haven't been booked yet. However I don't know how since I'd need to search the bookings table but that of course is populated with existing bookings i.e. tables that are NOT free.
Other than creating thousands of empty records with a booked Y/N field in which is obviously inefficient is there a way I can do this?
Ideally what I'd like to do is run a query with a parameter with a date which returns tables that haven't been booked on that date. Can you do this? Please say YES! Thanks

 

[pbaldy]

Not knowing your table structure, one thought is to have a query that returns the tables that are booked. Create a second query using the Unmatched query wizard that compares that against a table with all available tables in it. That should give you the available tables.

 

[seannypoos]

Brilliant - thanks. Almost there! I just tried it and it works great but how can I limit it to a specific date? At the moment it just (un)matches all tables so that basically if a table only has to be booked once in the past at any date or time for it NOT to show in the results

The structures are simply
"Tables"
Table ID
Table number
Location
No of seats

"Bookings"
BookingID
Table id (foreign key)
Date
Time
Special Requirements

Many many thanks for this
Sean

 

[pbaldy]

I would expect date (and perhaps time?) to be a criteria in that first query. I don't know if you plan to consider the length of time a table could be occupied. As currently designed, it looks like I could reserve a table at 7:15 even though it was already reserved at 7:00.

 

[seannypoos]

So - what if I get you right what I could do is make a query which searches the bookings table for a specific date and time (query1) and then do an unmatched query against query1 - is that right?

 

[pbaldy]

That's what I'm envisioning, yes. With that time conflict caveat of course.

 

[seannypoos]

AWESOME! Just tried it and it works a treat thank so very much
Cheers
Sean

 

[pbaldy]

Glad it works for you, Sean.