Relationship help and other advice

[Guest]

I have created a database in access 2002. I am familiar with flat databases
but when it comes to relationships it goes right over my head. I am trying
to organize a lot of people booking multiple hotels. Each hotel has multiple
categories and only so many rooms for each category. I had created a table
with the customers, then a table for each hotel and their categories. Then I
thought I would need a table for reservations to link both together but when
I tried the relationship thing nothing worked. I had added customer names
and added some hotels to test but I couldn't figure out what to next to get
the information added to each other. My ultimate goal is to connect
everything and then add a table to organize the rooming list when I get the
individuals names. I hope this makes sense to someone. If anyone has any
adive or samples I would really appreciate it

 

[John Vinson]

I have created a database in access 2002. I am familiar with flat databases
but when it comes to relationships it goes right over my head. I am trying
to organize a lot of people booking multiple hotels. Each hotel has multiple
categories and only so many rooms for each category. I had created a table
with the customers, then a table for each hotel and their categories. Then I
thought I would need a table for reservations to link both together but when
I tried the relationship thing nothing worked. I had added customer names
and added some hotels to test but I couldn't figure out what to next to get
the information added to each other. My ultimate goal is to connect
everything and then add a table to organize the rooming list when I get the
individuals names. I hope this makes sense to someone. If anyone has any
adive or samples I would really appreciate it

You'll need several tables here, not just two. Whenever you have a
many to many relationship as in this case (each room will be occupied,
over time, by multiple customers, and any individual customer might,
over time, book several rooms) you need a new table ("Bookings") to
represent the relationship. It will be related one-to-many to the
Rooms table and also to the Customers table.

I'd see the following tables: * indicates the Primary Key (which may
be one field or might be two or three).

Hotels
*HotelID
HotelName
<info about the hotel, e.g. address, phone, ...>

HotelCategories
*HotelID
*CategoryName

Rooms
*HotelID
*CategoryName
*RoomNo
Description
RackRate
<other info about the room itself, e.g. number of beds>

Customers
*CustomerID
LastName
FirstName
<etc etc>

Bookings
*CustomerID << link to Customers
*HotelID
*CategoryName
*RoomNo
DateIn
DateOut
<other info about this booking, e.g. special rates, number of
guests, etc.>


John W. Vinson[MVP]

 

[Guest]

Thank you John. This visual is exactly what I needed.