Hi

I have attached a diagram of the relationships (one to many) for this table.

The problem is where to put a GroupID and as of now the GroupID does not replicate in the tblBookings.

A group ID is needed as there can be as many as four persons per room and while travelling (motorcoach) there could be as many as 15 people who wish to sit near each other, and in this case assigning a group ID seems logical.

A GroupID and GroupName may make life easier (me) even for two people booking one room. Single bookings are rare.

What I am struggling with is where best this GroupID and GroupName belongs (table)?

After the tblCustomers is complete it would be then determined if a group was needed then enter it. Then I would have to have some kind of lookup where I could add persons to a group at a later time. To prevent typo's I would need to have some sort of query/list displaying the GroupName and then insert this into the appropriate table. (What table?)

I may be making this more complicated than it should be, but I thought the GroupID would replicate to the tblBookings without a problem.

I have tried several various table relationships without any progress.

Thanks

Attached Files

relationships.pdf (24.6 KB, 4 views)

tblCustomers should not be linked to tblBookings. The IDs do not automatically replicate, but they will in a form, subform, subsubform object.

Thanks and I corrected that.

I still cannot get tblGroup.GroupID to replicate to the tblBookings.GroupID and uncertain if I even need this?

Cheers

What are you doing that makes you think the IDs should be replicated in related tables?

I previously said

I though it may be easier to query if the GroupID was in the tblBookings?

I am using some subforms (dragging tables) but have no luck with the GroupID relationships. (tblBookings)

The idea is to assign a group ID and Group Name to those sharing a room. Up to four people can share one room (Single,Double,Triple,Quad).

There are also larger groups who may have 2 to a room and have 10 rooms allocated as well as sitting (Group) near each other in a motorcoach or doing something like going to see a show or event.

I have to submit occupancy reports to hotels (names, occupancy, groups and special requests) and generate reservation reports to customers giving them their booking details.

This is not a room reservation system but rather a small tour operators database.

Thanks

Queries will update the child and grandchild foreign keys appropriately.

Are your tables joined in your queries?

Post PDF(s) of your query(queries) in design view.

Check the relationship view of the tables and be sure that cascading updates and cascading deletes checkboxes are checked for the table relationships.

I don't have any query's made up yet as I have been stuck on wether my table design/relationships are correct?

I have worked with mySQL and simple access databases (html, cfml) previously, but the access 2007 interface and especially form handling is completely new to me.

Sorry to say I have spent days trying to figure this out (my bad!) but once I get the idea behind assigning those GroupID's to certain customers (not all) then I think I can create the forms/interface for data entry and some query's. It most likely not be usable to anyone else but myself until I get it user friendly, as time is closing in on me.

Thanks ...