Link two records in the same field using another table

[cammyjane10]

I'm creating a database for guests at a snowmobile lodge. I would like to create a new Guest in tbGuests but then have the option to create a second new GuestID linked to the first GuestID by a SpouseID. I can't seem to think out how they should be linked. So far I've created a separate table holding the SpouseID as primary key, GuestID and SpouseGuestID. Both the original guest and spouse need their own GuestID's so that I can post charges to each Guest separately if necessary. Then the SpouseID needs to be stored in the tbGuests to reference which guests are linked. Can anyone make sense of my mess?



Thanks in advance

[Beetle]

Maybe you're just over thinking it?

Why not just have a field (SpouseID) in tblGuests to store the PK value of the other guest and when you want to know who's married to who just do a self join?

[June7]

Why bother? What about other accompanying family members?

Have a table for all guests (including the primary) and link with ReservationID foreign key field.

[cammyjane10]

I figured that's what the problem was I was using spouse a little loosely as well. So then would I would create a new reservation using a table with ReservationID(pk) and GuestID. And then if I wanted to add more Guest ID's to that Reservation ID.....I would open a new reservation form but have the ReservationID match the preceding record? My brain just doesn't want to process this for some reason.

[June7]

Consider:

tblReservations
ResID
ResDate
ResRoom

tblGuests
GuestID
GuestLast
GuestFirst
GuestAddress
GuestPhone

tblGuestsRes
ResID
GuestID

If you don't care if a guest has visited previously then could maybe eliminate tblGuestRes and just put the ResID in tblGuests. This means an individual could have prior records but since this is not determined when the reservation is made, there could be inconsistency in data. So Martha Anne Jones could have a record under Martha Jones or Ann Jones or Marta Ann Jones (spelling errors).

[Beetle]

Both the original guest and spouse need their own GuestID's so that I can post charges to each Guest separately if necessary.

Just FYI - Using June7's example above, the field for these charges would go in the junction table (tblGuestRes) if you need to keep them separate for each Guest within a reservation.