Layout challenge...

[Protour Travel]

In my business (travel agency), I book mostly group hotel stays. Each hotel gets a rooming list that shows the guest's name, room preference, and arrival/departure dates. Here is my problem;

Lets assume there are ten guests in a group. Five are arriving on January-10 for 2 nights and the other five are arriving on January-11 for one night with all departing on January-12. Or for another scenario, all arrive and depart on the same days but two of the guests get suites and the remainder get regular rooms. I need to be able to place all these names on a single rooming list (hence the layout challenge) for both the benefit of both the hotel and the guests.

Here is what I have table/relationship wise.

4 tables (Hotel, Touring Group, Accommodations and Touring Personnel).

The relationships are; Hotel>-<Touring Group>-<Accommodations>-<Touring Personnel. Each relationship is linked through the GroupID key as below.

Now for the kicker. My homepage has a portal for the accommodations and another portal for touring personnel. I am trying to do this without leaving the homepage if possible. I am however, open to having a new window pop up with the touring personnel names (checkboxes) if need be.

The problem I am having is that whenever I go to a different accommodation (record), it changes the AccomSerNum and the arrival date on the previous one.

And if that isn't enough, I would like to have a 'select all' option on the personnel checkboxes for the occasions, albeit rare, that they all have the same room config, arrival/departure dates.

As usual, I hope this makes sense and thanks in advance,

Charlie

[comment]

I don't understand your relationships. For example, you have a GroupID_pk field in the Hotel table - that doesn't make any sense.

I think you should have:

Hotels -< Groups -< Guests

This is assuming each guest belongs to one group only, that the entire group is staying in the same hotel, and that the groups/guests arrive only once.

[Protour Travel]

This is assuming each guest belongs to one group only, that the entire group is staying in the same hotel, and that the groups/guests arrive only once.

The entire group doesn't always stay at the same hotel. Sometimes a group will utilize as many as four hotels per city. Example: one for the artist, one for the band, one for the road crew and sometimes another for the drivers.

I suppose when this happens we could assign a unique group name to each block of personnel. Bus A, Bus B, etc.

Your logic makes sense but before I go making any drastic changes let me add that each hotel in a given day gets the same HotelID_pk number which is a search aid for me. The ID numbers are dates formatted as 011211. I can easily pull up all the hotels used on the day in question by searching this term.

Also, your reply reads that I should have "Hotels -< Groups -< Guests".Are you saying I should drop the Touring Group to Accoms relationship and go directly from Touring Group to Touring Personnel?

If so, should I add a HotelSerNum_fk field to the Touring Group table and relate that way?

Would that help me in my Rooming List dilemma?

Thanks.

[comment]

I am not going to consider your list dilemma before I understand your relationships. After all, one is the product of the other.

If a group can be split between several hotels, then there is no (direct) relationship between Groups and Hotels. Each guest has their own hotel, and that's what the relationship should reflect:

Groups -< Guests >- Hotels

I am not sure what's in your Accommodations table and why it's needed. If all you have

is "the guest's name, room preference, and arrival/departure dates" - well, all of these are attributes of a guest and should be recorded in the Guests table.

If the hotel gives you a list of reserved rooms, and you need to assign guests to rooms, then you need another table for the rooms. This is assuming guests are not changing rooms mid-stay - otherwise it may get more complex.

each hotel in a given day gets the same HotelID_pk number which is a search aid for me. The ID numbers are dates formatted as 011211.

I am afraid you got me stumped there. A HotelID should uniquely identify a hotel - regardless of any date. What do you mean by "given date"?

[Protour Travel]

You wrote "I am not sure what's in your Accommodations table and why it's needed. If all you have is "the guest's name, room preference, and arrival/departure dates" - well, all of these are attributes of a guest and should be recorded in the Guests table."

The accommodations table has several fields relating to the number and types of rooms contracted for that stay. The fields include arrival/departure dates, # of nights, # of rooms, room configs and rates.

For example; you are getting 10 total rooms. 2 of these are suites for the artist and management. The remaining 8 are regular rooms. Or as shown in the snippet below, 4 guests are arriving on 11/19 and staying for two nights and 4 guests are arriving on 11/20 for only one night.

Each hotel gets a printed proposal from us that outlines needed accommodations. The hotels sales department uses this proposal as a guideline for drafting the contract. The following is a snippet of that proposal.

In answer to your reply, the personnel (guest) table does include a field for room preference however, the artist doesn't always get a suite. Depending on circumstances, mostly cost, sometimes they get a regular room also.

You wrote "If the hotel gives you a list of reserved rooms, and you need to assign guests to rooms, then..."

The hotel doesn't give us a list of reserved rooms. Once the contract is signed we furnish the hotel with a group rooming list (the list in question here) so they may assign a room to each tour member usually on the day of arrival.

This list includes the guest's name, a blank space for the hotel to hand write the assigned room number along with the guest's arrival/departure dates. (The group knows when they arrive and will depart but for some reason the hotels request that the arrive/depart dates be on the list.)

Below is a snippet of that list.

FYI, the list pictured does not work either. I have to manually insert the room config and arrive/depart dates. I am trying to automate this process as to prevent input mistakes by myself or any of my staff.

The HotelID_pk field is used purely as a search tool by me. It is the arrival date formatted as 011211 which in this case represents January 12, 2011. By using this generic system. If someone from artist management calls and wants the hotels for a certain day I can quick search using that field.

Bear in mind the FMP assigns each hotel a dedicated serial number (HotSerNum_pk).

One thing I have to say about the entertainment industry. It's different, sometimes bordering on weird. But it's never, ever boring.

Thanks again for all your attention to this matter.

BTW, I think I had a better reply before the forum sent south. I just can't remember how it was structured.

[comment]

I did read your lost reply and replied back. IIRC, I said something like "it's clearer now, but I still need walking through some of the steps". I'll try to reconstruct:

Let's have a worked example. We have "The FM Band" coming to town, with 4 members:

Name      Role      RoomType  Smoking   Arrival   Departure

Adam      Lead      Suite     1         x         y

Betty     Manager   Suite     0         x         y

Cecil     Drummer   Room      1         x         z

David     Roadie    Cellar    0         w         y

These are all guests' attributes and can be recorded in the Guests table. By summarizing the above we can produce the proposal in your example - except for the rates. I am not sure where these are coming from. Perhaps you need a table of RoomTypes (incl. rates) and select the RoomTypeID from there.

The next point that's not clear is what do you record after accepting a proposal (or proposals). In our example, the suites will be in Hotel A, and the other rooms in Hotel B. If you want to produce a rooming list as shown, you must enter the hotel assignments somewhere. This could be done by adding a HotelID field to the Guest's table.

If someone from artist management calls and wants the hotels for a certain day...

In the above arrangement, this would be done as follows:

1. Find the guests whose Arrival ≤ gDate ≤ Departure;

2. Go to Related Record [ from Hotels, Match found set ]

[Protour Travel]

Sheesh... I'm beginning if this is a pipe dream.

OK, I was able to get the proper names with the proper reservations by placing a value list (checkboxes) in the Accommodations portal.

This value list comes from the Touring Personnel table.

Here is a screenshot of the relationships graph as it stands now...

You will notice that the Hotel to Accommodations to Rooming List tables are all related by the AccomsSerNum field.

You will also notice that the Guest table has no relationship at this time.

When I select certain names in each accommodations record and print the rooming list it comes out looking like this...

instead of like this...

I know that FMP is smart enough to do this. I'm just not smart enough to make it happen. Or, am I wasting my our time?

[dansmith65]

The way I see it, you need to add a 'Locations' table, and restructure the Relationship Graph.

For example: Aerosmith does a Summer 2011 tour; this is entered into the Tour table. On this tour, there are 10 people who will travel with the tour (and will be at every location), and 1 guest appearance in San Francisco, making a total of 11 Tour Members, entered into the TourMembers table. This tour has a specific list of locations they will visit: San Francisco, Salt Lake City, and New York; these are all entered into the Locations table. For each location there is a 'guest' list (which is chosen from the TourMembers table). Each guest can stay in a different hotel at every location (or they can all stay in the same hotel, at every location).

Tour - TourMembers

     - Locations   - Guests - Hotel

Hotel

I'm not quite sure where TourVehicles fits in. I suspect it could be related to the TourMembers table.

I really have no idea what HotelSupport table is used for.

Since you don't know which room each Guest will be staying in until the day of (or after) they check-in, I suspect you don't need a RoomingList table. If you need to know this after the stay (for billing purposes, etc.), then it would be related to the Hotel table. If you want to specify how you will group guests into rooms before they arrive, you may want this table between the Guests and Hotel tables.

I doubt this is perfect, and it may seem wrong just because it is so different than your current setup, but try to think of how this basic setup could work.