My Specific Relationship Issues

[Guest]

2 weeks of trying to figure this out...i give up and seek your help!

Scenario: Small Backpackers Hostel

Goal of Database: To maintain records of all my guests and their stays at
my guests. Not to be a reservation system.

What I have done seemingly successfully:
A guest table (Parent? The One Side of the Relationship?) that contains the
following fields of importance to you guys:

Autonumber Field (Primary Key)
My Record Number (A number that I assign)
Last Name
First Name
Nationality
Passport Number

A Bookings Table (Child Table? The Many Side?)

Autonumber Field (Primary Key)
My Record Number (A number that I assign)
Date of Stay
Name of Room
Number of Nights
Price Per Night

A Room Table (Child Table?)

Name of Room (Primary Key)
Room Description
Price Per Night

My current relationships:

I have a referential integrity line connecting My Guest Table "Autonumber
Field" to My Booking Table "Our Record Number Field." On the Guest Table
Side of the line there is the small digit #1 and on my Bookings Table side
of the line there is a number 8 symbol lying down.

All of this stuff is apparently correctly connected to a form that I have
created that lets me enter all the data and is connected to a query.


The Problem:
For some reason, the form does not let me save the guest table data if I
have not filled anything in in the booking table fields. PERFECT! that is
what I want...not to be able to save a guest record if the guest does not
have any booking data. However the form DOES allow me to save the booking
data, even if I have not filled in any Guest Data fields. This is what I
have been struggling with for weeks! Why is the form allowing me to save the
booking data without the guest data? I will end up with orphans and I don't
want any!

I think that the problem is because I am having problems with the concepts
or perhaps I am trying to solve an unsolvable issue.

Can I get some relationship counseling?

Thank you SO MUCH!!!

 

[Guest]

I screwed up a bit...sorry. After checking again, I should clarify...I am
not able to save the form when it is all completely filled out because it
says this message:

Can not save or change this record because a related record is needed in the
table "Guest Table" (This is a rough translation from Spanish, because I am
using Access in Spanish...another problematic issue since Spanish is not my
first language!)

Yet I can save the form when only the booking fields are filled out.

And when I fill in only the fields that belong to the Guest Table table, I
get the error message The Database Motor Microsoft Jet of Data can not find
any record in the Room Table table with coinciding "Room" key fields. (again,
rough translation from Spanish)

Please help!!!

 

[John Vinson]

2 weeks of trying to figure this out...i give up and seek your help!

Comments inline.

Scenario: Small Backpackers Hostel

Goal of Database: To maintain records of all my guests and their stays at
my guests. Not to be a reservation system.

What I have done seemingly successfully:
A guest table (Parent? The One Side of the Relationship?) that contains the
following fields of importance to you guys:

Autonumber Field (Primary Key)
My Record Number (A number that I assign)

If your record number is stable, short, and unique, you might want to
consider making *it* the Primary Key. The PK should uniquely identify
a Guest, and I'd guess that this field does - no real need for another
field linked to it!

Last Name
First Name
Nationality
Passport Number

A Bookings Table (Child Table? The Many Side?)

Autonumber Field (Primary Key)
My Record Number (A number that I assign)

Is this the same record number as the Guests record number? Or what?
same argument applies.

Date of Stay
Name of Room
Number of Nights
Price Per Night

The Bookings table should also have a field defining a link to the
Guests table - otherwise you'll have no way to know who booked the
room!!! See below.

A Room Table (Child Table?)

Not really - it's another parent table, since one room will be used
(over time) for many guests' stays.

Name of Room (Primary Key)
Room Description
Price Per Night

My current relationships:

I have a referential integrity line connecting My Guest Table "Autonumber
Field" to My Booking Table "Our Record Number Field."

This won't work. You CANNOT ensure that the (arbitrarily assigned,
uncontrollable) autonumber will have any particular relationship to a
manually assigned Record Number. If you're using a Form (based on
Rooms) and Subform (based on bookings), use the Master/Child Link
Field to make the Room Number fill in; you'ld use a Combo Box to
select the value of the primary key of the Guests table into a field
of the same datatype as the Guest primary key in the Bookings table.
This would be a Long Integer if the guest primary key is an
Autonumber.

On the Guest Table
Side of the line there is the small digit #1 and on my Bookings Table side
of the line there is a number 8 symbol lying down.

All of this stuff is apparently correctly connected to a form that I have
created that lets me enter all the data and is connected to a query.

Don't use a Query joining the tables as the recordsource of the Form.
Instead, base the Form on a "one" side table - Rooms or Guests,
whichever is a better fit with your business - and use a Subform based
on Bookings, using the fields which link those tables as the master
and child link fields.

The Problem:
For some reason, the form does not let me save the guest table data if I
have not filled anything in in the booking table fields. PERFECT! that is
what I want...not to be able to save a guest record if the guest does not
have any booking data. However the form DOES allow me to save the booking
data, even if I have not filled in any Guest Data fields. This is what I
have been struggling with for weeks! Why is the form allowing me to save the
booking data without the guest data? I will end up with orphans and I don't
want any!

Because you're using a Query, instead of a Subform.

I think that the problem is because I am having problems with the concepts
or perhaps I am trying to solve an unsolvable issue.

Nope. Just use the right tools... a Subform in this case... and it's
much easier!

John W. Vinson[MVP]

 

[Guest]

Very helpful input.

Am starting the form design process all over again, now using subforms.

Still confused conceptually. One of the things that now confuses me is if
I should show in a subform or itself, the repeat stays of a guest (someone
who stays two nights, leaves, and comes back in a week for a night (or who
comes back even in two years!) Or should I show all the stays for one guest
in a report, should I ever want that information?

This part of your explanation baffled me...

"If you're using a Form (based on
Rooms) and Subform (based on bookings), use the Master/Child Link
Field to make the Room Number fill in; you'ld use a Combo Box to
select the value of the primary key of the Guests table into a field
of the same datatype as the Guest primary key in the Bookings table.
This would be a Long Integer if the guest primary key is an
Autonumber."

Could you break it down slower? If you could help me a bit more with the
concept and structure, then I think I could figure out the logistics of how
to make it pretty and work well.

Basically, now my new questions involve: how should my forms "nestle?"
Using my previous explanation, can you give me some hints? (At your
suggestion, I have now removed the autonumber field, and will be using my own
record number which will be a unique identifier and primary key field.)

Main Form/Guest Table
My Record Number
Last Name
First Name
Nationality
Passport Number

A Bookings Table (Subform)
My Record Number
Date of Stay
Name of Room
Number of Nights
Price Per Night
(Plus more pricing fields, like tourist tax etc)

A Room Table
Name of Room (Primary Key)
Room Description
Price Per Night

THANK YOU SO MUCH FOR YOUR HELP!

 

[John Vinson]

Very helpful input.

Am starting the form design process all over again, now using subforms.

Still confused conceptually. One of the things that now confuses me is if
I should show in a subform or itself, the repeat stays of a guest (someone
who stays two nights, leaves, and comes back in a week for a night (or who
comes back even in two years!) Or should I show all the stays for one guest
in a report, should I ever want that information?

Well... show whatever it is that you want to show. You know your
business model; I don't!

You can base a Report on a query selecting all the visits, all visits
within a date range, all contiguous visits, whatever you wish. Don't
confuse data STORAGE with data PRESENTATION - you store all the data,
and then selectively present what you want to see.

This part of your explanation baffled me...

"If you're using a Form (based on
Rooms) and Subform (based on bookings), use the Master/Child Link
Field to make the Room Number fill in; you'ld use a Combo Box to
select the value of the primary key of the Guests table into a field
of the same datatype as the Guest primary key in the Bookings table.
This would be a Long Integer if the guest primary key is an
Autonumber."

Could you break it down slower? If you could help me a bit more with the
concept and structure, then I think I could figure out the logistics of how
to make it pretty and work well.

Basically, now my new questions involve: how should my forms "nestle?"
Using my previous explanation, can you give me some hints? (At your
suggestion, I have now removed the autonumber field, and will be using my own
record number which will be a unique identifier and primary key field.)

Main Form/Guest Table
My Record Number
Last Name
First Name
Nationality
Passport Number

A Bookings Table (Subform)
My Record Number
Date of Stay
Name of Room
Number of Nights
Price Per Night
(Plus more pricing fields, like tourist tax etc)

A Room Table
Name of Room (Primary Key)
Room Description
Price Per Night

THANK YOU SO MUCH FOR YOUR HELP!

I guess the question is - when you're doing a booking, are you
entering (or selecting) the Guest info first, and then assigning that
guest to a room? Or are you choosing a vacant Room, and then assigning
a new guest to that room? You'ld use two different form designs for
these two cases. Assuming the former, you would have a Form based on
[Guests], with a Subform based on [Bookings]. If the Subform has [My
Record Number] as the Master Link Field and the Child Link Field, it
will automatically keep the mainform and the subform in synch,
displaying only that guest's bookings.

On the subform you would probably have a Combo Box control to select
the [Name of Room] (so you don't have to worry about selecting a
nonexistant room). You might need just a tiny bit of VBA code to COPY
the [Price Per Night] field from the Room table into the Bookings
table; this might seem redundant but it's not, since the price might
change over time or a customer might get a special rate.

John W. Vinson[MVP]