Hi,
I have been reading throguh various articles and learning a lot from you guys, so thank you very much. This place is a fantastic source of information, by far the best of anything i have found online.

I have a question which i would be grateful if someone could advise upon.

Access 2003. I have 4 tables:

list_accommodation (holding host family address details who offer accommodation to students)
id (autonum)
name (text)
address (text)
etc.....

list_rooms (table of rooms offered by host families)
room_id (autonum)
host_id (number) - joined to list_accommodation.id
room_name (text)
capacity (number)

students (student contact details who want accommodation!)
id (autonumber)
name (text)
dateofbirth (date)
address (text_
etc.....


bookings_accommodation (to hold the accomm bookings)
booking_id (autonum)
room_id (num) - joined to list_rooms.room_id
student_id (num) - joined to students.id
start_date (date)
end_date (date)

This structure therefore enables me to query if rooms are booked between dates:

SELECT bookings_accommodation.room_id, bookings_accommodation.start_date, bookings_accommodation.end_date
FROM list_rooms INNER JOIN bookings_accommodation ON list_rooms.room_id = bookings_accommodation.room_id
WHERE (((bookings_accommodation.start_date) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d",-1,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR ((([end_date]-1) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d","0",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR (((bookings_accommodation.start_date)<DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date])) AND (([end_date]-1)>DateAdd("d","-1",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date])));


and therefore, using the 'booked' query i can therefore find out rooms are available.

SELECT list_rooms.room_id, list_rooms.room_name, list_rooms.capacity, list_rooms.description, list_rooms.host_id
FROM list_rooms LEFT JOIN qry_rooms_reserved ON list_rooms.room_id = qry_rooms_reserved.room_id
WHERE (((qry_rooms_reserved.room_id) Is Null));

----

so, now i have found out that there is a necessity to enable students to share a room (the bookings for a room may not happen at the same time). This means that i have to determine the capacity of a room and therefore if
the capacity is not full, then show as available.

I was wondering what the best approach to this may be?
I have added 'capacity' to list_rooms, but i am unsure as to how to go from there....

Any thoughts and advice woudl be really appreciated.

Many thanks

Phil.

Consider adding a table where the count of people moving in and out of rooms are represented by dated values ...
Table tOccupancyEvent
EventID, PK
RoomID, FK
'BookingID, FK ???
EventDate
MoveInCount
When a booking is made add two records to this table. For both records identify the RoomID or perhaps the BookingID. In addition ...
Record 1
EventDate = start date of the booking
MoveInCount = how many people move into the room, say 1
Record 2
EventDate = end date of the booking
MoveInCount = how many people move into the room, say -1.
Now you can easily...
SELECT Sum(MoveInCount) As Occupancy
FROM tOccupancyEvent
WHERE EventDate <= #SomeDate#
AND RoomID = <SomeRoomID>
which shows you how many people occupy any particular room on any particular day. This is easily compared with your "list_rooms.Capacity" to establish vacancies at the room level.
Your bookings table might also need a GuestCount field if anyone ever books for two or more.
Cheers,

many thanks for the prompt reply. I shall investigate further, but on the face of it seems exactly what i was after. thanks again.

Phil.