Client List

The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more than one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in 2 months
Lisa comes back without Jim. but now is travelling with Melissa and stays in
Room B for 3 nights. The next day Jim comes back alone and stays in a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command button.

Lisa has her own client number (autonumber in the client info table) with
all her personal data and a stay number (autonumber in the stay table) with
all the pertinent stay information. Jim's info I add in another "friend"
table, and he gets assigned his own unique client number (autonumber with the
prefix F to show he is a friend, or secondary person in the room) The same
scenario will happen when Lisa comes back with Melissa. And when Jim comes
back on his own, he gets entered again as a new "Main" guest and will finally
get his "own" client number, and maybe one day he will have some friends that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one guest
comes back to my hostel with a completely different guest, my info reflects a
new stay number with the new friend who has their own friend client number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't HAVE to
be listed in the separate friend table just because I haven't figured out how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my clients, I
would have to run the query or report by stay number (the only number they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest table
with room for entering 3 different individuals and manually assigning them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff solvable once
I learn how to set up reports and queries?

by separating the clients into different tables based on whether they're a
"main client" or a "friend" on a given stay, you're de-normalizing your
table design rather than normalizing it. if you have a tblFriends, and a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals, however you
want to name it). enter each guest as a record, whether the guest is the
person paying for the room, or just a friend tagging along for free. since
one guest can have many stays, and one stay can have many guests, you have a
many-to-many relationship between those two entities. you'll need a junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a trivial
project you've undertaken. to do it correctly, and therefore efficiently,
you need to really understand the principles of relational data modeling. i
didn't see your previous thread; but if nobody recommended that you study
relational data modeling already, then i strongly recommend that you do so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_...tels/index.htm.

note that the data model does not show a many-to-many relationship between
guests and bookings. this is because the only "person" that's stored in that
table is the person booking (paying for) the room. in your case, you're
storing data about everyone who stays in a room, not just the booker/payer -
hence the many-to-many relationship.

note that using somebody else's data model can be a great timesaver - but it
*does not* replace the necessity of learning the principles of relational
data modeling for yourself. you are in a much better position to analyze
your business process and determine the best tables/relationships design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn when
building a relational database, and most people struggle with it. but it's
well worth the time you have to invest in learning to "do it right"; in the
long run, the time you spend on tables/relationships will be more than saved
on the time spent building the rest of the database.

hth


"andreainpanama" wrote in message
...
The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more than
one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in 2
months
Lisa comes back without Jim. but now is travelling with Melissa and stays
in
Room B for 3 nights. The next day Jim comes back alone and stays in a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command button.

Lisa has her own client number (autonumber in the client info table) with
all her personal data and a stay number (autonumber in the stay table)
with
all the pertinent stay information. Jim's info I add in another "friend"
table, and he gets assigned his own unique client number (autonumber with
the
prefix F to show he is a friend, or secondary person in the room) The
same
scenario will happen when Lisa comes back with Melissa. And when Jim
comes
back on his own, he gets entered again as a new "Main" guest and will
finally
get his "own" client number, and maybe one day he will have some friends
that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one guest
comes back to my hostel with a completely different guest, my info
reflects a
new stay number with the new friend who has their own friend client
number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't HAVE
to
be listed in the separate friend table just because I haven't figured out
how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my clients,
I
would have to run the query or report by stay number (the only number they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest table
with room for entering 3 different individuals and manually assigning them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff solvable
once
I learn how to set up reports and queries?

You are absolutely right with what you are saying about normalization. I
can't believe that I lost track of it!

So there is no question that I will convert my set up (thank goodness only
have to change about 40 records) to having the one sole guest table set up
and I will keep it that each new entry will receive a unique autonumber
client number without F prefixes. I will connect individual guests who
happen to be staying together at the same time with a unique stay autonumber.

So now the question is...how can I automatically assign more than one guest
the same stay autonumber, or will just the first entry have it assigned
automatically and the second and third guests have a field where I manually
fill in the same number?

Thanks so much !

"tina" wrote:

by separating the clients into different tables based on whether they're a
"main client" or a "friend" on a given stay, you're de-normalizing your
table design rather than normalizing it. if you have a tblFriends, and a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals, however you
want to name it). enter each guest as a record, whether the guest is the
person paying for the room, or just a friend tagging along for free. since
one guest can have many stays, and one stay can have many guests, you have a
many-to-many relationship between those two entities. you'll need a junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a trivial
project you've undertaken. to do it correctly, and therefore efficiently,
you need to really understand the principles of relational data modeling. i
didn't see your previous thread; but if nobody recommended that you study
relational data modeling already, then i strongly recommend that you do so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_...tels/index.htm.

note that the data model does not show a many-to-many relationship between
guests and bookings. this is because the only "person" that's stored in that
table is the person booking (paying for) the room. in your case, you're
storing data about everyone who stays in a room, not just the booker/payer -
hence the many-to-many relationship.

note that using somebody else's data model can be a great timesaver - but it
*does not* replace the necessity of learning the principles of relational
data modeling for yourself. you are in a much better position to analyze
your business process and determine the best tables/relationships design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn when
building a relational database, and most people struggle with it. but it's
well worth the time you have to invest in learning to "do it right"; in the
long run, the time you spend on tables/relationships will be more than saved
on the time spent building the rest of the database.

hth


"andreainpanama" wrote in message
...
The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more than
one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in 2
months
Lisa comes back without Jim. but now is travelling with Melissa and stays
in
Room B for 3 nights. The next day Jim comes back alone and stays in a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command button.

Lisa has her own client number (autonumber in the client info table) with
all her personal data and a stay number (autonumber in the stay table)
with
all the pertinent stay information. Jim's info I add in another "friend"
table, and he gets assigned his own unique client number (autonumber with
the
prefix F to show he is a friend, or secondary person in the room) The
same
scenario will happen when Lisa comes back with Melissa. And when Jim
comes
back on his own, he gets entered again as a new "Main" guest and will
finally
get his "own" client number, and maybe one day he will have some friends
that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one guest
comes back to my hostel with a completely different guest, my info
reflects a
new stay number with the new friend who has their own friend client
number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't HAVE
to
be listed in the separate friend table just because I haven't figured out
how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my clients,
I
would have to run the query or report by stay number (the only number they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest table
with room for entering 3 different individuals and manually assigning them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff solvable
once
I learn how to set up reports and queries?

i'm not sure if we're having a two-way communication problem, or if you're
not understanding the table setup i described elsewhere in this post.

you have a table listing all guests, tblGuests.
you have a table listing all stays, tblStays.
both of the above are parent tables, in a parent-child relationship with
tblStay_Guests.

so if you have one stay record with a StayID of 247, and three guests
associated with that stay, and their GuestID's are 13, 37, and 182, then
there will be three records in tblStay_Guests:

StayGuestID StayID GuestID
1 247 13
2 247 37
3 247 182

in the above example, StayGuestID is the primary key field of
tblStay_Guests; StayID is a foreign key field that is linked to tblStays;
and GuestID is a foreign key field that is linked to tblGuests.

hth


"andreainpanama" wrote in message
...
You are absolutely right with what you are saying about normalization. I
can't believe that I lost track of it!

So there is no question that I will convert my set up (thank goodness only
have to change about 40 records) to having the one sole guest table set up
and I will keep it that each new entry will receive a unique autonumber
client number without F prefixes. I will connect individual guests who
happen to be staying together at the same time with a unique stay
autonumber.

So now the question is...how can I automatically assign more than one
guest
the same stay autonumber, or will just the first entry have it assigned
automatically and the second and third guests have a field where I
manually
fill in the same number?

Thanks so much !

"tina" wrote:

by separating the clients into different tables based on whether they're
a
"main client" or a "friend" on a given stay, you're de-normalizing your
table design rather than normalizing it. if you have a tblFriends, and a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals, however
you
want to name it). enter each guest as a record, whether the guest is the
person paying for the room, or just a friend tagging along for free.
since
one guest can have many stays, and one stay can have many guests, you
have a
many-to-many relationship between those two entities. you'll need a
junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a trivial
project you've undertaken. to do it correctly, and therefore
efficiently,
you need to really understand the principles of relational data
modeling. i
didn't see your previous thread; but if nobody recommended that you
study
relational data modeling already, then i strongly recommend that you do
so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_...tels/index.htm.

note that the data model does not show a many-to-many relationship
between
guests and bookings. this is because the only "person" that's stored in
that
table is the person booking (paying for) the room. in your case, you're
storing data about everyone who stays in a room, not just the
booker/payer -
hence the many-to-many relationship.

note that using somebody else's data model can be a great timesaver -
but it
*does not* replace the necessity of learning the principles of
relational
data modeling for yourself. you are in a much better position to analyze
your business process and determine the best tables/relationships
design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn
when
building a relational database, and most people struggle with it. but
it's
well worth the time you have to invest in learning to "do it right"; in
the
long run, the time you spend on tables/relationships will be more than
saved
on the time spent building the rest of the database.

hth


"andreainpanama" wrote in message
...
The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more
than
one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in 2
months
Lisa comes back without Jim. but now is travelling with Melissa and
stays
in
Room B for 3 nights. The next day Jim comes back alone and stays in a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command
button.

Lisa has her own client number (autonumber in the client info table)
with
all her personal data and a stay number (autonumber in the stay table)
with
all the pertinent stay information. Jim's info I add in another
"friend"
table, and he gets assigned his own unique client number (autonumber
with
the
prefix F to show he is a friend, or secondary person in the room) The
same
scenario will happen when Lisa comes back with Melissa. And when Jim
comes
back on his own, he gets entered again as a new "Main" guest and will
finally
get his "own" client number, and maybe one day he will have some
friends
that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one
guest
comes back to my hostel with a completely different guest, my info
reflects a
new stay number with the new friend who has their own friend client
number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim
and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't
HAVE
to
be listed in the separate friend table just because I haven't figured
out
how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my
clients,
I
would have to run the query or report by stay number (the only number
they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest
table
with room for entering 3 different individuals and manually assigning
them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff solvable
once
I learn how to set up reports and queries?

If it helps you to visualise what Tinas geek speak is trying to tell you,
you do not want to be searching for John Smith in two different places. Main
Guest or friend they are people that stayed at your hotel. Whether they were
a main guest or a friend depends on which stay (transaction) you are talking
about, so that info belongs in a separate table linking the guest with a
stay. Few programmers try to run hostels ....


"tina" wrote in message
...
by separating the clients into different tables based on whether they're a
"main client" or a "friend" on a given stay, you're de-normalizing your
table design rather than normalizing it. if you have a tblFriends, and a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals, however
you
want to name it). enter each guest as a record, whether the guest is the
person paying for the room, or just a friend tagging along for free. since
one guest can have many stays, and one stay can have many guests, you have
a
many-to-many relationship between those two entities. you'll need a
junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a trivial
project you've undertaken. to do it correctly, and therefore efficiently,
you need to really understand the principles of relational data modeling.
i
didn't see your previous thread; but if nobody recommended that you study
relational data modeling already, then i strongly recommend that you do
so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_...tels/index.htm.

note that the data model does not show a many-to-many relationship
between
guests and bookings. this is because the only "person" that's stored in
that
table is the person booking (paying for) the room. in your case, you're
storing data about everyone who stays in a room, not just the
booker/payer -
hence the many-to-many relationship.

note that using somebody else's data model can be a great timesaver - but
it
*does not* replace the necessity of learning the principles of relational
data modeling for yourself. you are in a much better position to analyze
your business process and determine the best tables/relationships design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn when
building a relational database, and most people struggle with it. but it's
well worth the time you have to invest in learning to "do it right"; in
the
long run, the time you spend on tables/relationships will be more than
saved
on the time spent building the rest of the database.

hth


"andreainpanama" wrote in message
...
The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more than
one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in 2
months
Lisa comes back without Jim. but now is travelling with Melissa and stays
in
Room B for 3 nights. The next day Jim comes back alone and stays in a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command button.

Lisa has her own client number (autonumber in the client info table) with
all her personal data and a stay number (autonumber in the stay table)
with
all the pertinent stay information. Jim's info I add in another "friend"
table, and he gets assigned his own unique client number (autonumber with
the
prefix F to show he is a friend, or secondary person in the room) The
same
scenario will happen when Lisa comes back with Melissa. And when Jim
comes
back on his own, he gets entered again as a new "Main" guest and will
finally
get his "own" client number, and maybe one day he will have some friends
that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one guest
comes back to my hostel with a completely different guest, my info
reflects a
new stay number with the new friend who has their own friend client
number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't HAVE
to
be listed in the separate friend table just because I haven't figured out
how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my
clients,
I
would have to run the query or report by stay number (the only number
they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest table
with room for entering 3 different individuals and manually assigning
them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff solvable
once
I learn how to set up reports and queries?

Thanks Tina. I am admittedly a bit slow on the uptake and I do suffer from
reading comprehension so bear with me. Sometimes I don't grasp concepts so
quickly.

I actually did have a junction table set up...with only two
fields....foreign keys from the two different tables, one is guestid, and the
other is stayid. They were set up as a composite primary key.

At your suggestion (not really sure why but I did it anyway...), I have
added the third field and called it stayid_guestid, and I have made IT the
primary key. I think I have everything set up.

Now the question is, what do I do with it? Do I fill in the data manually?
What is my best form set up for this new design?

Advise if now I should post in a different area.

And thanks David for extra support. My hostel is located in the city of
David, Panama! (go to page www.purplehousehostel.com to see!)

"David Cox" wrote:

If it helps you to visualise what Tinas geek speak is trying to tell you,
you do not want to be searching for John Smith in two different places. Main
Guest or friend they are people that stayed at your hotel. Whether they were
a main guest or a friend depends on which stay (transaction) you are talking
about, so that info belongs in a separate table linking the guest with a
stay. Few programmers try to run hostels ....


"tina" wrote in message
...
by separating the clients into different tables based on whether they're a
"main client" or a "friend" on a given stay, you're de-normalizing your
table design rather than normalizing it. if you have a tblFriends, and a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals, however
you
want to name it). enter each guest as a record, whether the guest is the
person paying for the room, or just a friend tagging along for free. since
one guest can have many stays, and one stay can have many guests, you have
a
many-to-many relationship between those two entities. you'll need a
junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a trivial
project you've undertaken. to do it correctly, and therefore efficiently,
you need to really understand the principles of relational data modeling.
i
didn't see your previous thread; but if nobody recommended that you study
relational data modeling already, then i strongly recommend that you do
so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_...tels/index.htm.

note that the data model does not show a many-to-many relationship
between
guests and bookings. this is because the only "person" that's stored in
that
table is the person booking (paying for) the room. in your case, you're
storing data about everyone who stays in a room, not just the
booker/payer -
hence the many-to-many relationship.

note that using somebody else's data model can be a great timesaver - but
it
*does not* replace the necessity of learning the principles of relational
data modeling for yourself. you are in a much better position to analyze
your business process and determine the best tables/relationships design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn when
building a relational database, and most people struggle with it. but it's
well worth the time you have to invest in learning to "do it right"; in
the
long run, the time you spend on tables/relationships will be more than
saved
on the time spent building the rest of the database.

hth


"andreainpanama" wrote in message
...
The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more than
one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in 2
months
Lisa comes back without Jim. but now is travelling with Melissa and stays
in
Room B for 3 nights. The next day Jim comes back alone and stays in a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command button.

Lisa has her own client number (autonumber in the client info table) with
all her personal data and a stay number (autonumber in the stay table)
with
all the pertinent stay information. Jim's info I add in another "friend"
table, and he gets assigned his own unique client number (autonumber with
the
prefix F to show he is a friend, or secondary person in the room) The
same
scenario will happen when Lisa comes back with Melissa. And when Jim
comes
back on his own, he gets entered again as a new "Main" guest and will
finally
get his "own" client number, and maybe one day he will have some friends
that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one guest
comes back to my hostel with a completely different guest, my info
reflects a
new stay number with the new friend who has their own friend client
number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't HAVE
to
be listed in the separate friend table just because I haven't figured out
how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my
clients,
I
would have to run the query or report by stay number (the only number
they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest table
with room for entering 3 different individuals and manually assigning
them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff solvable
once
I learn how to set up reports and queries?

comments inline.

"andreainpanama" wrote in message
...
Thanks Tina. I am admittedly a bit slow on the uptake and I do suffer
from
reading comprehension so bear with me. Sometimes I don't grasp concepts
so
quickly.

no problem.


I actually did have a junction table set up...with only two
fields....foreign keys from the two different tables, one is guestid, and
the
other is stayid. They were set up as a composite primary key.

At your suggestion (not really sure why but I did it anyway...), I have
added the third field and called it stayid_guestid, and I have made IT the
primary key. I think I have everything set up.

okay. your initial two-field primary key was fine; you can either keep the
current table design or go back to the two-field design. i personally don't
like multi-field primary keys, at least in a table that is or may become a
"parent" table, linked to some child table - so i automatically think in
terms of single-field keys. but there is nothing wrong with using a
multi-field primary key, as long as all the fields used as the composite key
are required to make the key unique.


Now the question is, what do I do with it? Do I fill in the data
manually?
What is my best form set up for this new design?

okay, now that we have the design/relationships of these three tables laid
out, it's (somewhat) safe to talk about data entry forms. usually you set up
a form to follow the flow of the business process it supports. again, you're
in a better position to determine that, than i am.

i'm guessing that you'll want to enter a "stay" record, and include details
of the guests who are associated with that stay - new and/or return guests.
bearing in mind the limits of your current form development/coding skill
levels, i'd suggest going with a standard setup for supporting the tables in
a many-to-many relationship: 1) create a form bound to tblGuests, where you
can add new guest records; i'll call it frmGuests. 2) create another form
bound to tblStay_Guests; i'll call it sfrmStay_Guests. on this form, create
a combo box control with the *RowSource* property set to tblGuests. (if
you're not familiar with creating combo box controls, read up on them in
Access Help; if you run into problems, post back with the actual field names
in tblGuests and i'll walk you thru the setup.) set the LimitToList property
to Yes. in the NotInList event procedure, add code to open frmGuests, add a
new guest record, update the combo box droplist, and enter the guest in the
combo box. 3) create a form bound to tblStays; i'll call it frmStays. on
this form, add a subform control from the Toolbox toolbar. i'll call the
subform control ChildGuests. set the control's properties as follows:

SourceObject: sfrmStay_Guests
LinkChildFields: StayID
LinkMasterFields: StayID

now, when you open frmStays to add a record, you can immediately assign the
guest(s) to that stay, in the subform. if you need to assign a guest who is
not in the combo box droplist on the subform, the NotInList event will open
frmGuests so you can add the new guest record to that subform "on the fly",
and then continue working in frmStays.

hth


Advise if now I should post in a different area.

And thanks David for extra support. My hostel is located in the city of
David, Panama! (go to page www.purplehousehostel.com to see!)

"David Cox" wrote:

If it helps you to visualise what Tinas geek speak is trying to tell
you,
you do not want to be searching for John Smith in two different places.
Main
Guest or friend they are people that stayed at your hotel. Whether they
were
a main guest or a friend depends on which stay (transaction) you are
talking
about, so that info belongs in a separate table linking the guest with a
stay. Few programmers try to run hostels ....


"tina" wrote in message
...
by separating the clients into different tables based on whether
they're a
"main client" or a "friend" on a given stay, you're de-normalizing
your
table design rather than normalizing it. if you have a tblFriends, and
a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals,
however
you
want to name it). enter each guest as a record, whether the guest is
the
person paying for the room, or just a friend tagging along for free.
since
one guest can have many stays, and one stay can have many guests, you
have
a
many-to-many relationship between those two entities. you'll need a
junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a
trivial
project you've undertaken. to do it correctly, and therefore
efficiently,
you need to really understand the principles of relational data
modeling.
i
didn't see your previous thread; but if nobody recommended that you
study
relational data modeling already, then i strongly recommend that you
do
so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_...tels/index.htm.

note that the data model does not show a many-to-many relationship
between
guests and bookings. this is because the only "person" that's stored
in
that
table is the person booking (paying for) the room. in your case,
you're
storing data about everyone who stays in a room, not just the
booker/payer -
hence the many-to-many relationship.

note that using somebody else's data model can be a great timesaver -
but
it
*does not* replace the necessity of learning the principles of
relational
data modeling for yourself. you are in a much better position to
analyze
your business process and determine the best tables/relationships
design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn
when
building a relational database, and most people struggle with it. but
it's
well worth the time you have to invest in learning to "do it right";
in
the
long run, the time you spend on tables/relationships will be more than
saved
on the time spent building the rest of the database.

hth


"andreainpanama" wrote in message
...
The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more
than
one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in
2
months
Lisa comes back without Jim. but now is travelling with Melissa and
stays
in
Room B for 3 nights. The next day Jim comes back alone and stays in
a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command
button.

Lisa has her own client number (autonumber in the client info table)
with
all her personal data and a stay number (autonumber in the stay
table)
with
all the pertinent stay information. Jim's info I add in another
"friend"
table, and he gets assigned his own unique client number (autonumber
with
the
prefix F to show he is a friend, or secondary person in the room)
The
same
scenario will happen when Lisa comes back with Melissa. And when Jim
comes
back on his own, he gets entered again as a new "Main" guest and will
finally
get his "own" client number, and maybe one day he will have some
friends
that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one
guest
comes back to my hostel with a completely different guest, my info
reflects a
new stay number with the new friend who has their own friend client
number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim
and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't
HAVE
to
be listed in the separate friend table just because I haven't figured
out
how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my
clients,
I
would have to run the query or report by stay number (the only number
they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest
table
with room for entering 3 different individuals and manually assigning
them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff
solvable
once
I learn how to set up reports and queries?

Hi Tina: I actually have had most of what you recommended already done in
terms of form setup. However for the last few days, (in another discussion
group,) I have been working on my NotInList event problem. Once I get that
going...then I can continue the final touches.

This is the code I am working with: (This was all given to me with the help
of another person, I know nothing about vb code so I am literally cutting and
pasting)

Private Sub cboMainGuestLastName_NotInList(NewData As String, Response As
Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In MainGuestInfo " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO MainGuestInfo (MainGuestLastName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtMainGuestGender.SetFocus
Response = acDataErrAdded
Else
Me.cboMainGuestLastName.Undo
Response = acDataErrContinue
End If

End Sub

It isn't working for some reason. This is a rundown of exactly what I am
doing.

I go into the properties of MainGuestLastName.
I click the 3 dots next to the Not in List event.
It automatically opens a visual basic window that already has a lot of code
in it.
I delete all that code.
I go to the little list in the upper left window and choose my field,
MainGuestLastName. It automatically registers something in the little window
on the upper right that says, Before Update and it puts a line of code in the
box. I change the option from Before Update to Not in List and again, it
puts a line of code in, but this time what looks to be the first line of code
that you gave me. I then cut and paste the remaining portion of the code
that I have from you. This following line appears in red highlight.

rst.FindFirst "[MainGuestLastName] = '" & NewData & "'" And
MainGuestFirstName Is Null,

I save it all, and close all the vb stuff. Then when I go back to my form
entry mode, and I enter something in the field that is not in list...as soon
as I tab to the next field...it automatically opens up (from my data entry
form), a new visual basic code window. With an error message (rough
translation from Spanish (I am using the Spanish version of access, which
adds to my problems)....compilation error, syntax error.

And now this line appears in yellow highlight.
Private Sub MainGuestLastName_NotInList(NewData As String, Response As
Integer)

Any chance you might be able to help me with my notinlist problem?

Thanks!

"tina" wrote:

comments inline.

"andreainpanama" wrote in message
...
Thanks Tina. I am admittedly a bit slow on the uptake and I do suffer
from
reading comprehension so bear with me. Sometimes I don't grasp concepts
so
quickly.

no problem.


I actually did have a junction table set up...with only two
fields....foreign keys from the two different tables, one is guestid, and
the
other is stayid. They were set up as a composite primary key.

At your suggestion (not really sure why but I did it anyway...), I have
added the third field and called it stayid_guestid, and I have made IT the
primary key. I think I have everything set up.

okay. your initial two-field primary key was fine; you can either keep the
current table design or go back to the two-field design. i personally don't
like multi-field primary keys, at least in a table that is or may become a
"parent" table, linked to some child table - so i automatically think in
terms of single-field keys. but there is nothing wrong with using a
multi-field primary key, as long as all the fields used as the composite key
are required to make the key unique.


Now the question is, what do I do with it? Do I fill in the data
manually?
What is my best form set up for this new design?

okay, now that we have the design/relationships of these three tables laid
out, it's (somewhat) safe to talk about data entry forms. usually you set up
a form to follow the flow of the business process it supports. again, you're
in a better position to determine that, than i am.

i'm guessing that you'll want to enter a "stay" record, and include details
of the guests who are associated with that stay - new and/or return guests.
bearing in mind the limits of your current form development/coding skill
levels, i'd suggest going with a standard setup for supporting the tables in
a many-to-many relationship: 1) create a form bound to tblGuests, where you
can add new guest records; i'll call it frmGuests. 2) create another form
bound to tblStay_Guests; i'll call it sfrmStay_Guests. on this form, create
a combo box control with the *RowSource* property set to tblGuests. (if
you're not familiar with creating combo box controls, read up on them in
Access Help; if you run into problems, post back with the actual field names
in tblGuests and i'll walk you thru the setup.) set the LimitToList property
to Yes. in the NotInList event procedure, add code to open frmGuests, add a
new guest record, update the combo box droplist, and enter the guest in the
combo box. 3) create a form bound to tblStays; i'll call it frmStays. on
this form, add a subform control from the Toolbox toolbar. i'll call the
subform control ChildGuests. set the control's properties as follows:

SourceObject: sfrmStay_Guests
LinkChildFields: StayID
LinkMasterFields: StayID

now, when you open frmStays to add a record, you can immediately assign the
guest(s) to that stay, in the subform. if you need to assign a guest who is
not in the combo box droplist on the subform, the NotInList event will open
frmGuests so you can add the new guest record to that subform "on the fly",
and then continue working in frmStays.

hth


Advise if now I should post in a different area.

And thanks David for extra support. My hostel is located in the city of
David, Panama! (go to page www.purplehousehostel.com to see!)

"David Cox" wrote:

If it helps you to visualise what Tinas geek speak is trying to tell
you,
you do not want to be searching for John Smith in two different places.
Main
Guest or friend they are people that stayed at your hotel. Whether they
were
a main guest or a friend depends on which stay (transaction) you are
talking
about, so that info belongs in a separate table linking the guest with a
stay. Few programmers try to run hostels ....


"tina" wrote in message
...
by separating the clients into different tables based on whether
they're a
"main client" or a "friend" on a given stay, you're de-normalizing
your
table design rather than normalizing it. if you have a tblFriends, and
a
tblClients, then you're putting data into tablenames ("friend", and
"client") which violates normalization rules.

recommend a single table for guests (or people, or individuals,
however
you
want to name it). enter each guest as a record, whether the guest is
the
person paying for the room, or just a friend tagging along for free.
since
one guest can have many stays, and one stay can have many guests, you
have
a
many-to-many relationship between those two entities. you'll need a
junction
table, like tblStay_Guests, which is on the many side of a one-to-many
relationship with tblGuests and on the many side of a one-to-many
relationship with tblStays (or bookings, or whatever you call it).

to quote myself from one of your previous posts: "this is not a
trivial
project you've undertaken. to do it correctly, and therefore
efficiently,
you need to really understand the principles of relational data
modeling.
i
didn't see your previous thread; but if nobody recommended that you
study
relational data modeling already, then i strongly recommend that you
do
so.
for more information, see
http://home.att.net/~california.db/tips.html#aTip1.
also suggest you take a look at the hotel reservations model at
http://www.databaseanswers.org/data_...tels/index.htm.

note that the data model does not show a many-to-many relationship
between
guests and bookings. this is because the only "person" that's stored
in
that
table is the person booking (paying for) the room. in your case,
you're
storing data about everyone who stays in a room, not just the
booker/payer -
hence the many-to-many relationship.

note that using somebody else's data model can be a great timesaver -
but
it
*does not* replace the necessity of learning the principles of
relational
data modeling for yourself. you are in a much better position to
analyze
your business process and determine the best tables/relationships
design,
than anyone in these newsgroups - once you've learned how to do it."

if you've been studying relational design, then you need to continue -
you're not quite there yet. don't be discouraged; relational design
principles, or normalization, is the most difficult concept to learn
when
building a relational database, and most people struggle with it. but
it's
well worth the time you have to invest in learning to "do it right";
in
the
long run, the time you spend on tables/relationships will be more than
saved
on the time spent building the rest of the database.

hth


"andreainpanama" wrote in message
...
The backpacker hostel in Panama again.

What is the best way to organize my unique client numbers when more
than
one
guest stays in the same room? For example:

If Lisa and Jim both stay in room D for 2 days in April, and then in
2
months
Lisa comes back without Jim. but now is travelling with Melissa and
stays
in
Room B for 3 nights. The next day Jim comes back alone and stays in
a
different room.

Right now what I have is:

The client info table is the main form
The stay table is a sub form in data sheet view
The friend table is a form that pops up when I push its command
button.

Lisa has her own client number (autonumber in the client info table)
with
all her personal data and a stay number (autonumber in the stay
table)
with
all the pertinent stay information. Jim's info I add in another
"friend"
table, and he gets assigned his own unique client number (autonumber
with
the
prefix F to show he is a friend, or secondary person in the room)
The
same
scenario will happen when Lisa comes back with Melissa. And when Jim
comes
back on his own, he gets entered again as a new "Main" guest and will
finally
get his "own" client number, and maybe one day he will have some
friends
that
will get assigned the F friend number.

So, I have successfully figured out a way to make sure that if one
guest
comes back to my hostel with a completely different guest, my info
reflects a
new stay number with the new friend who has their own friend client
number.
It's all fine and I am amazed that I actually set this up!!!!

The problem is theoretical. The truth is that in both scenarios, Jim
and
Melissa are just as much as a "Main" guest as Lisa. They shouldn't
HAVE
to
be listed in the separate friend table just because I haven't figured
out
how
to give them their own unique number in the main client form!

It all works OK from a data entry point of view, but I am concerned,
because looking at the future, if I ever want a listing of all my
clients,
I
would have to run the query or report by stay number (the only number
they
have in common), as opposed to dates of stay or name order.

I was so interested in normalizing my data, that I separated the
individuals, it might have just been simpler to have one long guest
table
with room for entering 3 different individuals and manually assigning
them
incremental guest ids.

Am I creating problems where there aren't any. Is this stuff
solvable
once
I learn how to set up reports and queries?