Multiple Tables, Dont return entry if it appears in both tables. Plz Help

[jazzy639]

Hello,

I am making a Room Booking System. The user selects a date on th
calendar, then a period (1-6) and then the user should see a page wit
the avaibale rooms (exluding rooms which are permanently used and one
which already have been booked on the same date at the same period)
Once the user has decided on what room to book (out of the returne
rooms), it adds an entry to the Booked table.

The entered Date, Day and Period are carried on throughout the fe
pages.

Here are what my two access tables look like:

_*Main*_

_Day__________Period________Permanent___________Room_
Monday 1 No D13
Monday 1 Yes D14
Monday 1 No A17
... for every Room
Monday 2 Yes D13
Monday 2 No D14
... for every Room
... for every Period
... for every Day



_*Booked*_

I will enter in some test data here

_Date_______________Day_______________Period______________Room_
25/04/05 Monday 1 D13
27/04/05 Wednesday 4 B12


If a user was to select 25/04/05 (which is a Monday) and Period 1, th
SQL statement should take that data in, search the Main table to se
which rooms are not permanently booked, then check to see if any o
those rooms are in the Booked table, with the date the user has entere
(this is carried forward to this page).

It should see:

Mon P1: D13 D14 A17
Not Permanently booked: D13 A17
CHECK BOOKED TABL
On the 25/04/05, P1, Room D13 is booked
_Exclude_Room_D13_form_results_
RETURN ROOM A1

I can already get it to return the available rooms from the Main table
I just dont know how to get it to search the Booked table with th
results form the main table.

Any help would be greatly appreciated.

Thanks,

Jazz

 

[jazzy639]

Hello,

For a visual of what it looks like please have a look at th
following.

Please select *29/04/05* and then *Period 1*

http://roomcommander.somee.com/calender.asp

You will see that I now have it returning the booked rooms aswell. I
it possible to change the SQL so that when _main.room_ = _booked.room_
it doesnt display any of them so that we are left with only the room
available