Showing Data thats not there!?!

[Kerry Purdy]

Hi

I have a system which allows us to enter our room hire & training bookings
room requirements to ensure we have reserved the rooms we need.

I have 2 tables:-
Room Data (Room ID, Room Name, Capacity, Cost)
Rm Req (RBID, TBID, Room ID, Hire Date, Start Time, End Time)
RBID=Room Booking reference number if used for room hire
TBID=Training Booking refernce number if used for training course

The Rm Req table is filled with the hire date, start & end time, Room ID and
either and RBID number or a TBID number.

What I need to do is when I am entering a new booking, I would like to
select the drop down box and only those rooms that are available.

I have created a query which quite happily tells me what rooms are being
used on a specified date but not rooms are available. The trouble is that
the rooms only appear in Rm req if they are being used.

I hope this make sense, does anyone have any ideas how I can show a list of
Rooms available on a specified date?

Many thanks for your time.

Kerry

 

[kingston via AccessMonster.com]

If rooms are available only for entire days, you can use a sub-query based on
the query you already have for what is being used. Your main query would
return all of the rooms, and with the NOT IN clause, you can exclude all of
the rooms being used.

SELECT [Room Name]... FROM [Room Data]
WHERE [Room ID] NOT IN (SELECT Room ID...)

The part in parentheses is the query you've already built to show what's in
use on the day in question.

 

[Kerry Purdy]

Hiya

I have created a query based on my "Qry Rooms in Use" query. where is the
NOT IN entered? In the SQL or in the grid, I have tried both but i am only
copying and pasting your example, as I don't really understand what I am
doing.

Please advise, many thanks for your time.

Kerry

If rooms are available only for entire days, you can use a sub-query based on
the query you already have for what is being used. Your main query would
return all of the rooms, and with the NOT IN clause, you can exclude all of
the rooms being used.

SELECT [Room Name]... FROM [Room Data]
WHERE [Room ID] NOT IN (SELECT Room ID...)

The part in parentheses is the query you've already built to show what's in
use on the day in question.

Hi

I have a system which allows us to enter our room hire & training bookings
room requirements to ensure we have reserved the rooms we need.

I have 2 tables:-
Room Data (Room ID, Room Name, Capacity, Cost)
Rm Req (RBID, TBID, Room ID, Hire Date, Start Time, End Time)
RBID=Room Booking reference number if used for room hire
TBID=Training Booking refernce number if used for training course

The Rm Req table is filled with the hire date, start & end time, Room ID and
either and RBID number or a TBID number.

What I need to do is when I am entering a new booking, I would like to
select the drop down box and only those rooms that are available.

I have created a query which quite happily tells me what rooms are being
used on a specified date but not rooms are available. The trouble is that
the rooms only appear in Rm req if they are being used.

I hope this make sense, does anyone have any ideas how I can show a list of
Rooms available on a specified date?

Many thanks for your time.

Kerry

 

[kingston via AccessMonster.com]

The easiest way to do this is to create a query with a subquery visually and
then change to SQL design view. Create a query and copy the underlying SQL.
Then create another query and paste the SQL as a criteria for a field.
Change to SQL view for the main query. Look for the subquery condition and
change the conditional to NOT IN (probably from IN).

Hiya

I have created a query based on my "Qry Rooms in Use" query. where is the
NOT IN entered? In the SQL or in the grid, I have tried both but i am only
copying and pasting your example, as I don't really understand what I am
doing.

Please advise, many thanks for your time.

Kerry

If rooms are available only for entire days, you can use a sub-query based on
the query you already have for what is being used. Your main query would

[quoted text clipped - 34 lines]