Relationships for Booking System

[Puffet]

Hi,

I have seen many examples of a booking system designed in Access but the situation I need is a little different and I can't seem to get my Tables and Relationships Right.

I need to set up the System so that One Booking can have many guests and each guest is allocated a room. The issue is that Guests are repeat Guests so each guest can have many room allocations.

I deally I want a main form which is the booking form and then a subform where I have the Guest and Room allocation.

here is the way I have set-it up now and it looks too complex (and does't work the way I want it to).

Any comments greatly appreciated.

Puffet

 

[neileg]

Doesn't look complex to me. How is not working?

 

[Puffet]

Thanks for the reply,

Well I need it so that I can have as follows:

I create a booking
In this booking I attach a room and attached to each room is one or more guests.

I presume I need a booking table, a Guest table and a Room table but how do I link these up so that I can have this relationship.

Puffet

 

[Mr. B]

Puffet,

This is a clasic case of the many-to-many relationship situation. This is where you have to have another table that will hold the foreign key values from your Guest table, your Bookings table, ane your room table. This table should also have it's own PK just for reference purposes later on.

In some cases, when you are creating data like this, you may have to actually create records using VBA after users have made selections.

HTH

 

[Puffet]

Hi Mr B

Great. As per your suggestion I tried an additional table with many keys and it is working like a charm.

Much appreciated

Puffet

 

[Mr. B]

Glad to help. Sometimes you just need someone to give you a nudge in the right direction. Many-to-many relationships seem to give a lot of folk problems.

Good luck with your project.