Which is better..an Auto Lookup Feature or a Multiple Column Combo

[andreainpanama]

So confused.

I own a small bed and breakfast in Panama. I want to enter all the data for
all my past, present and future guests into a database, using a form. The
form is now designed, more or less the way I want it. The form is called
Guest List Form. This is my question.

If I have a client, Joe Smith, and he stayed in say "Room A", I add his
name and all his personal data to my list. When I plug in "Room A" I want
all the pertinent information about room A to pop up, and where necessary,
perform calculations.

I have the following fields in the Guest List Table, for example (this is a
simplified version)>

Name
Passport Number
Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

So, for example, if I put in Room A, I want it to show
dorm, shared bath, $6.00
If I put in Room B
I want to show private room, private bath, $20.00

Then, once the price pops in, I want all the tax and totals to be able to be
calculated.

Not knowing what is my best option, I also have another table created,
called Room List, that has all these fields

Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

Using all of Access examples, I am trying to think of my rooms as products,
for example, room C will always be a six room dorm bed, for $6.00 with a
shared bath.

Once you all tell me which is my better option, then I might need some help.
I have been working on this for so long my head is spinning.

 

[Graham Mandeno]

Hi Andrea

You should not be duplicating information about, for example, the type of
room in each guest booking record.

I would recommend not just two, but three tables:

Guests:
GuestID (this could be an autonumber)
FirstName
LastName
PassportNumber
... etc
(this table contains ONLY information about people, not rooms or
anything else)

Rooms:
RoomNumber
Description
Bathroom
RoomPrice

And a third table...

Bookings:
GuestID
RoomNumber
DateIn
DateOut
Price

Notice that the Bookings table has no guest data or room data, only a
GuestID and RoomNumber, which link the record to the related records in the
other tables.

The ONLY field that is "duplicated" is Price, and that is because it is what
is known as "point-in-time data". The price of a stay in a given room may
change from time to time, so you want to record the price *at the time of
the stay*. Also, you may choose to give discounts for loyal guests, or
whatever...

Which brings me to the point of having a separate Guests table. If you have
a guest returning for a second or third stay, you want to take advantage of
the fact that you already have their details on record, and not duplicate
all your data entry. This will also allow you to create queries to find
guests who are returning for multiple stays, etc.

Even though the data is stored in three different tables, you can easily
create a query to view all the information for a particular booking,
including guest details and room details, by including all three related
tables in your query.

Post back if you're still confused and need more help
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[andreainpanama]

Thanks so much Graham. I will get to work at separating my tables, as you
suggested. I will post again here soon, to let you know how it is going, and
to get a bit of help with that "easily created query" you refered to!

Hi Andrea

You should not be duplicating information about, for example, the type of
room in each guest booking record.

I would recommend not just two, but three tables:

Guests:
GuestID (this could be an autonumber)
FirstName
LastName
PassportNumber
... etc
(this table contains ONLY information about people, not rooms or
anything else)

Rooms:
RoomNumber
Description
Bathroom
RoomPrice

And a third table...

Bookings:
GuestID
RoomNumber
DateIn
DateOut
Price

Notice that the Bookings table has no guest data or room data, only a
GuestID and RoomNumber, which link the record to the related records in the
other tables.

The ONLY field that is "duplicated" is Price, and that is because it is what
is known as "point-in-time data". The price of a stay in a given room may
change from time to time, so you want to record the price *at the time of
the stay*. Also, you may choose to give discounts for loyal guests, or
whatever...

Which brings me to the point of having a separate Guests table. If you have
a guest returning for a second or third stay, you want to take advantage of
the fact that you already have their details on record, and not duplicate
all your data entry. This will also allow you to create queries to find
guests who are returning for multiple stays, etc.

Even though the data is stored in three different tables, you can easily
create a query to view all the information for a particular booking,
including guest details and room details, by including all three related
tables in your query.

Post back if you're still confused and need more help
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

So confused.

I own a small bed and breakfast in Panama. I want to enter all the data
for
all my past, present and future guests into a database, using a form. The
form is now designed, more or less the way I want it. The form is called
Guest List Form. This is my question.

If I have a client, Joe Smith, and he stayed in say "Room A", I add his
name and all his personal data to my list. When I plug in "Room A" I want
all the pertinent information about room A to pop up, and where necessary,
perform calculations.

I have the following fields in the Guest List Table, for example (this is
a
simplified version)>

Name
Passport Number
Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

So, for example, if I put in Room A, I want it to show
dorm, shared bath, $6.00
If I put in Room B
I want to show private room, private bath, $20.00

Then, once the price pops in, I want all the tax and totals to be able to
be
calculated.

Not knowing what is my best option, I also have another table created,
called Room List, that has all these fields

Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

Using all of Access examples, I am trying to think of my rooms as
products,
for example, room C will always be a six room dorm bed, for $6.00 with a
shared bath.

Once you all tell me which is my better option, then I might need some
help.
I have been working on this for so long my head is spinning.

 

[andreainpanama]

OK, I have now separated my tables. Do you recommend that I try to edit my
current existing form that was based on my one large table, or should I start
from scratch. Would this be a time to use "subforms?"

Hi Andrea

You should not be duplicating information about, for example, the type of
room in each guest booking record.

I would recommend not just two, but three tables:

Guests:
GuestID (this could be an autonumber)
FirstName
LastName
PassportNumber
... etc
(this table contains ONLY information about people, not rooms or
anything else)

Rooms:
RoomNumber
Description
Bathroom
RoomPrice

And a third table...

Bookings:
GuestID
RoomNumber
DateIn
DateOut
Price

Notice that the Bookings table has no guest data or room data, only a
GuestID and RoomNumber, which link the record to the related records in the
other tables.

The ONLY field that is "duplicated" is Price, and that is because it is what
is known as "point-in-time data". The price of a stay in a given room may
change from time to time, so you want to record the price *at the time of
the stay*. Also, you may choose to give discounts for loyal guests, or
whatever...

Which brings me to the point of having a separate Guests table. If you have
a guest returning for a second or third stay, you want to take advantage of
the fact that you already have their details on record, and not duplicate
all your data entry. This will also allow you to create queries to find
guests who are returning for multiple stays, etc.

Even though the data is stored in three different tables, you can easily
create a query to view all the information for a particular booking,
including guest details and room details, by including all three related
tables in your query.

Post back if you're still confused and need more help
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

So confused.

I own a small bed and breakfast in Panama. I want to enter all the data
for
all my past, present and future guests into a database, using a form. The
form is now designed, more or less the way I want it. The form is called
Guest List Form. This is my question.

If I have a client, Joe Smith, and he stayed in say "Room A", I add his
name and all his personal data to my list. When I plug in "Room A" I want
all the pertinent information about room A to pop up, and where necessary,
perform calculations.

I have the following fields in the Guest List Table, for example (this is
a
simplified version)>

Name
Passport Number
Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

So, for example, if I put in Room A, I want it to show
dorm, shared bath, $6.00
If I put in Room B
I want to show private room, private bath, $20.00

Then, once the price pops in, I want all the tax and totals to be able to
be
calculated.

Not knowing what is my best option, I also have another table created,
called Room List, that has all these fields

Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

Using all of Access examples, I am trying to think of my rooms as
products,
for example, room C will always be a six room dorm bed, for $6.00 with a
shared bath.

Once you all tell me which is my better option, then I might need some
help.
I have been working on this for so long my head is spinning.

 

[Graham Mandeno]

Great to hear you are on the way to having a "normalised database"!

Do be sure (if you haven't already) to go into the relationships window
(Tools > Relationships) and set up one-to-many relationships between Guests
and Bookings, and between Rooms and Bookings.

Now, the design of your queries, forms, and reports depends very much on
their purpose. I don't know how much work you've put in to your existing
form or what it looks like, but it's probably easier to start from scratch.

To create a query that shows fields from all three tables, open a new query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up the
relationships correctly. Then you can drag fields from each table down onto
the query grid.

For a form to add bookings, you might have a form with a combo to select the
room (bound to Bookings.RoomNumber and with a RowSource based on Rooms).
The AfterUpdate event for RoomNumber (occurs after a room is selected) would
copy Rooms.RoomPrice into Bookings.Price as an initial or default value
(which you can change if you wish). Other fields from the Rooms table
(Description and Bathroom for example) can be included in the query and be
bound to textboxes on your form. They will "magically" fill in as soon as a
room is selected. You should make them locked and disabled though, so that
someone cannot inadvertently change the bathroom status of a room while
making a booking.

Selecting the guest could also be done by a combo box, but you might
(hopefully!) end up with thousands of records in your Guests table which
would make it difficult to distinguish between John Smith from Australia and
John Smith from New York. It might be better to have a "Lookup Guest"
button which opens another form. This form would allow you to enter a name
and/or passport number and/or address/phone ... whatever ... to see if the
person making a booking is already in your database. If not, then the
"lookup" form could have another button to "Add New Guest" which would open
a form bound to the Guests table. Closing the lookup form would fill in the
selected (or new) GuestID in the booking record.

For a start, to get it working and increase your confidence, I suggest you
use a combo box to select the guest, and have a button to add a new guest if
they are not in the list.

Later on, as well as the Lookup Guest form, you might also want to get fancy
with popup calendars to select the dates of the stay. You could also
calculate the total tariff from the number of nights (worked out from the
dates) and the RoomPrice. Also (getting very fancy now!) you could limit
your room selection combo to only those rooms which are not already booked
for the required nights.

Also, based on the same three-table query, you can print out a daily
check-in report of all the guests who are expected to arrive that day.

Lots to do - have fun!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, I have now separated my tables. Do you recommend that I try to edit
my
current existing form that was based on my one large table, or should I
start
from scratch. Would this be a time to use "subforms?"

Hi Andrea

You should not be duplicating information about, for example, the type of
room in each guest booking record.

I would recommend not just two, but three tables:

Guests:
GuestID (this could be an autonumber)
FirstName
LastName
PassportNumber
... etc
(this table contains ONLY information about people, not rooms or
anything else)

Rooms:
RoomNumber
Description
Bathroom
RoomPrice

And a third table...

Bookings:
GuestID
RoomNumber
DateIn
DateOut
Price

Notice that the Bookings table has no guest data or room data, only a
GuestID and RoomNumber, which link the record to the related records in
the
other tables.

The ONLY field that is "duplicated" is Price, and that is because it is
what
is known as "point-in-time data". The price of a stay in a given room
may
change from time to time, so you want to record the price *at the time of
the stay*. Also, you may choose to give discounts for loyal guests, or
whatever...

Which brings me to the point of having a separate Guests table. If you
have
a guest returning for a second or third stay, you want to take advantage
of
the fact that you already have their details on record, and not duplicate
all your data entry. This will also allow you to create queries to find
guests who are returning for multiple stays, etc.

Even though the data is stored in three different tables, you can easily
create a query to view all the information for a particular booking,
including guest details and room details, by including all three related
tables in your query.

Post back if you're still confused and need more help
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

So confused.

I own a small bed and breakfast in Panama. I want to enter all the
data
for
all my past, present and future guests into a database, using a form.
The
form is now designed, more or less the way I want it. The form is
called
Guest List Form. This is my question.

If I have a client, Joe Smith, and he stayed in say "Room A", I add
his
name and all his personal data to my list. When I plug in "Room A" I
want
all the pertinent information about room A to pop up, and where
necessary,
perform calculations.

I have the following fields in the Guest List Table, for example (this
is
a
simplified version)>

Name
Passport Number
Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

So, for example, if I put in Room A, I want it to show
dorm, shared bath, $6.00
If I put in Room B
I want to show private room, private bath, $20.00

Then, once the price pops in, I want all the tax and totals to be able
to
be
calculated.

Not knowing what is my best option, I also have another table created,
called Room List, that has all these fields

Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

Using all of Access examples, I am trying to think of my rooms as
products,
for example, room C will always be a six room dorm bed, for $6.00 with
a
shared bath.

Once you all tell me which is my better option, then I might need some
help.
I have been working on this for so long my head is spinning.

 

[andreainpanama]

Thank you so much for your help!

Part of my problem is that the access version that I have is in Spanish, and
English is my first language, so I am trying to figure this all out in
another language!

Let me address your first point...

I am having a hard time understanding if I am joining my tables correctly in
the one to many relationship. First, the concepts...one guest can have many
stays, so there is a one to many that is clear to me. But I can-t conceive
of the one to many relationship in my bookings/room tables. Can you help me
with the concepts? Also, should each field be matched with a same name field?

Great to hear you are on the way to having a "normalised database"!

Do be sure (if you haven't already) to go into the relationships window
(Tools > Relationships) and set up one-to-many relationships between Guests
and Bookings, and between Rooms and Bookings.

Now, the design of your queries, forms, and reports depends very much on
their purpose. I don't know how much work you've put in to your existing
form or what it looks like, but it's probably easier to start from scratch.

To create a query that shows fields from all three tables, open a new query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up the
relationships correctly. Then you can drag fields from each table down onto
the query grid.

For a form to add bookings, you might have a form with a combo to select the
room (bound to Bookings.RoomNumber and with a RowSource based on Rooms).
The AfterUpdate event for RoomNumber (occurs after a room is selected) would
copy Rooms.RoomPrice into Bookings.Price as an initial or default value
(which you can change if you wish). Other fields from the Rooms table
(Description and Bathroom for example) can be included in the query and be
bound to textboxes on your form. They will "magically" fill in as soon as a
room is selected. You should make them locked and disabled though, so that
someone cannot inadvertently change the bathroom status of a room while
making a booking.

Selecting the guest could also be done by a combo box, but you might
(hopefully!) end up with thousands of records in your Guests table which
would make it difficult to distinguish between John Smith from Australia and
John Smith from New York. It might be better to have a "Lookup Guest"
button which opens another form. This form would allow you to enter a name
and/or passport number and/or address/phone ... whatever ... to see if the
person making a booking is already in your database. If not, then the
"lookup" form could have another button to "Add New Guest" which would open
a form bound to the Guests table. Closing the lookup form would fill in the
selected (or new) GuestID in the booking record.

For a start, to get it working and increase your confidence, I suggest you
use a combo box to select the guest, and have a button to add a new guest if
they are not in the list.

Later on, as well as the Lookup Guest form, you might also want to get fancy
with popup calendars to select the dates of the stay. You could also
calculate the total tariff from the number of nights (worked out from the
dates) and the RoomPrice. Also (getting very fancy now!) you could limit
your room selection combo to only those rooms which are not already booked
for the required nights.

Also, based on the same three-table query, you can print out a daily
check-in report of all the guests who are expected to arrive that day.

Lots to do - have fun!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, I have now separated my tables. Do you recommend that I try to edit
my
current existing form that was based on my one large table, or should I
start
from scratch. Would this be a time to use "subforms?"

Hi Andrea

You should not be duplicating information about, for example, the type of
room in each guest booking record.

I would recommend not just two, but three tables:

Guests:
GuestID (this could be an autonumber)
FirstName
LastName
PassportNumber
... etc
(this table contains ONLY information about people, not rooms or
anything else)

Rooms:
RoomNumber
Description
Bathroom
RoomPrice

And a third table...

Bookings:
GuestID
RoomNumber
DateIn
DateOut
Price

Notice that the Bookings table has no guest data or room data, only a
GuestID and RoomNumber, which link the record to the related records in
the
other tables.

The ONLY field that is "duplicated" is Price, and that is because it is
what
is known as "point-in-time data". The price of a stay in a given room
may
change from time to time, so you want to record the price *at the time of
the stay*. Also, you may choose to give discounts for loyal guests, or
whatever...

Which brings me to the point of having a separate Guests table. If you
have
a guest returning for a second or third stay, you want to take advantage
of
the fact that you already have their details on record, and not duplicate
all your data entry. This will also allow you to create queries to find
guests who are returning for multiple stays, etc.

Even though the data is stored in three different tables, you can easily
create a query to view all the information for a particular booking,
including guest details and room details, by including all three related
tables in your query.

Post back if you're still confused and need more help
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



So confused.

I own a small bed and breakfast in Panama. I want to enter all the
data
for
all my past, present and future guests into a database, using a form.
The
form is now designed, more or less the way I want it. The form is
called
Guest List Form. This is my question.

If I have a client, Joe Smith, and he stayed in say "Room A", I add
his
name and all his personal data to my list. When I plug in "Room A" I
want
all the pertinent information about room A to pop up, and where
necessary,
perform calculations.

I have the following fields in the Guest List Table, for example (this
is
a
simplified version)>

Name
Passport Number
Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

So, for example, if I put in Room A, I want it to show
dorm, shared bath, $6.00
If I put in Room B
I want to show private room, private bath, $20.00

Then, once the price pops in, I want all the tax and totals to be able
to
be
calculated.

Not knowing what is my best option, I also have another table created,
called Room List, that has all these fields

Room # (This is where I want the automatic stuff to start)
Description (single, private, dorm)
Bathroom (shared, private)
Price (This is the calculated field)

Using all of Access examples, I am trying to think of my rooms as
products,
for example, room C will always be a six room dorm bed, for $6.00 with
a
shared bath.

Once you all tell me which is my better option, then I might need some
help.
I have been working on this for so long my head is spinning.

 

[Graham Mandeno]

I'm not familiar with Spanish, sorry, not the Spanish version of Access!

However, to answer your immediate questions, each room may be used for many
stays - hence the one-to-many relationship there. Of course, these stays
cannot occur at the same time, but that is another issue - one of
scheduling.

Actually, come to think of it, I think you should add an autonumber primary
key to your Bookings/Stays table. That number could be used as a reference
number for bookings and as an invoice number also.

And no, the related fields in the two different tables do not need to have
the same name.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you so much for your help!

Part of my problem is that the access version that I have is in Spanish,
and
English is my first language, so I am trying to figure this all out in
another language!

Let me address your first point...

I am having a hard time understanding if I am joining my tables correctly
in
the one to many relationship. First, the concepts...one guest can have
many
stays, so there is a one to many that is clear to me. But I can-t
conceive
of the one to many relationship in my bookings/room tables. Can you help
me
with the concepts? Also, should each field be matched with a same name
field?

Great to hear you are on the way to having a "normalised database"!

Do be sure (if you haven't already) to go into the relationships window
(Tools > Relationships) and set up one-to-many relationships between
Guests
and Bookings, and between Rooms and Bookings.

Now, the design of your queries, forms, and reports depends very much on
their purpose. I don't know how much work you've put in to your existing
form or what it looks like, but it's probably easier to start from
scratch.

To create a query that shows fields from all three tables, open a new
query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up the
relationships correctly. Then you can drag fields from each table down
onto
the query grid.

For a form to add bookings, you might have a form with a combo to select
the
room (bound to Bookings.RoomNumber and with a RowSource based on Rooms).
The AfterUpdate event for RoomNumber (occurs after a room is selected)
would
copy Rooms.RoomPrice into Bookings.Price as an initial or default value
(which you can change if you wish). Other fields from the Rooms table
(Description and Bathroom for example) can be included in the query and
be
bound to textboxes on your form. They will "magically" fill in as soon
as a
room is selected. You should make them locked and disabled though, so
that
someone cannot inadvertently change the bathroom status of a room while
making a booking.

Selecting the guest could also be done by a combo box, but you might
(hopefully!) end up with thousands of records in your Guests table which
would make it difficult to distinguish between John Smith from Australia
and
John Smith from New York. It might be better to have a "Lookup Guest"
button which opens another form. This form would allow you to enter a
name
and/or passport number and/or address/phone ... whatever ... to see if
the
person making a booking is already in your database. If not, then the
"lookup" form could have another button to "Add New Guest" which would
open
a form bound to the Guests table. Closing the lookup form would fill in
the
selected (or new) GuestID in the booking record.

For a start, to get it working and increase your confidence, I suggest
you
use a combo box to select the guest, and have a button to add a new guest
if
they are not in the list.

Later on, as well as the Lookup Guest form, you might also want to get
fancy
with popup calendars to select the dates of the stay. You could also
calculate the total tariff from the number of nights (worked out from the
dates) and the RoomPrice. Also (getting very fancy now!) you could limit
your room selection combo to only those rooms which are not already
booked
for the required nights.

Also, based on the same three-table query, you can print out a daily
check-in report of all the guests who are expected to arrive that day.

Lots to do - have fun!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[andreainpanama]

OK...you said

"To create a query that shows fields from all three tables, open a new query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up the
relationships correctly."

First question...why do I need a query...why aren't I just designing a form
that refers to my three tables. Is it possible that I don't really
understand the concept of queries?

Two, I did it anyway, but apparently I don't have my relationships set up
correctly because the line is not bold and when I save the query and try to
reopen it, it says that my field types don't match. (I double checked
several times, and frankly, just don't know where the problem is.) When I
double click on the line, it does say that i have one to many relationships,
even though I only connected one field to one field in each table, and have
only one join line connecting each table, or should I connect every single
field I want to be participating in the join and have many join lines from
one table to another?

In some ways I feel that i am an intermediate user..., but this spanish
issue is causing me to have to relearn everything. I have not had any
success in finding a programmer here in my area to get some local help. I
have an old access program, 97 which I refer to for its English instructions,
but I am not sure how much has changed.

Just let me know if I am taking up too much of your time! And thank you so
much for your help!

Thanks!

I'm not familiar with Spanish, sorry, not the Spanish version of Access!

However, to answer your immediate questions, each room may be used for many
stays - hence the one-to-many relationship there. Of course, these stays
cannot occur at the same time, but that is another issue - one of
scheduling.

Actually, come to think of it, I think you should add an autonumber primary
key to your Bookings/Stays table. That number could be used as a reference
number for bookings and as an invoice number also.

And no, the related fields in the two different tables do not need to have
the same name.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you so much for your help!

Part of my problem is that the access version that I have is in Spanish,
and
English is my first language, so I am trying to figure this all out in
another language!

Let me address your first point...

I am having a hard time understanding if I am joining my tables correctly
in
the one to many relationship. First, the concepts...one guest can have
many
stays, so there is a one to many that is clear to me. But I can-t
conceive
of the one to many relationship in my bookings/room tables. Can you help
me
with the concepts? Also, should each field be matched with a same name
field?

Great to hear you are on the way to having a "normalised database"!

Do be sure (if you haven't already) to go into the relationships window
(Tools > Relationships) and set up one-to-many relationships between
Guests
and Bookings, and between Rooms and Bookings.

Now, the design of your queries, forms, and reports depends very much on
their purpose. I don't know how much work you've put in to your existing
form or what it looks like, but it's probably easier to start from
scratch.

To create a query that shows fields from all three tables, open a new
query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up the
relationships correctly. Then you can drag fields from each table down
onto
the query grid.

For a form to add bookings, you might have a form with a combo to select
the
room (bound to Bookings.RoomNumber and with a RowSource based on Rooms).
The AfterUpdate event for RoomNumber (occurs after a room is selected)
would
copy Rooms.RoomPrice into Bookings.Price as an initial or default value
(which you can change if you wish). Other fields from the Rooms table
(Description and Bathroom for example) can be included in the query and
be
bound to textboxes on your form. They will "magically" fill in as soon
as a
room is selected. You should make them locked and disabled though, so
that
someone cannot inadvertently change the bathroom status of a room while
making a booking.

Selecting the guest could also be done by a combo box, but you might
(hopefully!) end up with thousands of records in your Guests table which
would make it difficult to distinguish between John Smith from Australia
and
John Smith from New York. It might be better to have a "Lookup Guest"
button which opens another form. This form would allow you to enter a
name
and/or passport number and/or address/phone ... whatever ... to see if
the
person making a booking is already in your database. If not, then the
"lookup" form could have another button to "Add New Guest" which would
open
a form bound to the Guests table. Closing the lookup form would fill in
the
selected (or new) GuestID in the booking record.

For a start, to get it working and increase your confidence, I suggest
you
use a combo box to select the guest, and have a button to add a new guest
if
they are not in the list.

Later on, as well as the Lookup Guest form, you might also want to get
fancy
with popup calendars to select the dates of the stay. You could also
calculate the total tariff from the number of nights (worked out from the
dates) and the RoomPrice. Also (getting very fancy now!) you could limit
your room selection combo to only those rooms which are not already
booked
for the required nights.

Also, based on the same three-table query, you can print out a daily
check-in report of all the guests who are expected to arrive that day.

Lots to do - have fun!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Andrea

Answers inline...

OK...you said

"To create a query that shows fields from all three tables, open a new
query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up the
relationships correctly."

First question...why do I need a query...why aren't I just designing a
form
that refers to my three tables. Is it possible that I don't really
understand the concept of queries?

A query is the means by which you can bring together fields from related
tables. You can create a form which has a SQL statement as its record
source (SELECT Field1, Field2 ... from Table1 inner join Table2 on ...) but
this is still a query. The only difference is that this query is not saved
so that it can be used for other purposes, such as reports.

Two, I did it anyway, but apparently I don't have my relationships set up
correctly because the line is not bold and when I save the query and try
to
reopen it, it says that my field types don't match. (I double checked
several times, and frankly, just don't know where the problem is.) When I
double click on the line, it does say that i have one to many
relationships,
even though I only connected one field to one field in each table, and
have
only one join line connecting each table, or should I connect every
single
field I want to be participating in the join and have many join lines from
one table to another?

Related fields must be of the same type. You can't create a relation
between a numeric field and a text field, or even between two numeric fields
of a different size (for instance Integer and Long). The one special case
is with AutoNumber fields. These are frequently used as primary keys. The
foreign key field related to an AutoNumber must be a Long Integer field.

In some ways I feel that i am an intermediate user..., but this spanish
issue is causing me to have to relearn everything. I have not had any
success in finding a programmer here in my area to get some local help.
I
have an old access program, 97 which I refer to for its English
instructions,
but I am not sure how much has changed.

Just let me know if I am taking up too much of your time! And thank you
so
much for your help!

You're very welcome! And no, you are not taking up too much time

 

[andreainpanama]

Hi Graham,

Well by now, I have continued to work on my form, and I am thinking it is
pretty much where I want it. Half the stuff, I don-t know how I have gotten
to work...but somehow they are! I have a few more issues that really don't
fall into my original posting subject line, but since you have been so prompt
and helpful, I wanted to run them by you first.

First of all, for some reason, none of my combo box selections seem to
remain in my form or my tables after I close them and reopen them.

Also, how can I reset the autonumbering to start from 1 again?

Next I will enter about 10 records and start to work on my reports. Stay
tuned!

Thanks!

Hi Andrea

Answers inline...

OK...you said

"To create a query that shows fields from all three tables, open a new
query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up the
relationships correctly."

First question...why do I need a query...why aren't I just designing a
form
that refers to my three tables. Is it possible that I don't really
understand the concept of queries?

A query is the means by which you can bring together fields from related
tables. You can create a form which has a SQL statement as its record
source (SELECT Field1, Field2 ... from Table1 inner join Table2 on ...) but
this is still a query. The only difference is that this query is not saved
so that it can be used for other purposes, such as reports.

Two, I did it anyway, but apparently I don't have my relationships set up
correctly because the line is not bold and when I save the query and try
to
reopen it, it says that my field types don't match. (I double checked
several times, and frankly, just don't know where the problem is.) When I
double click on the line, it does say that i have one to many
relationships,
even though I only connected one field to one field in each table, and
have
only one join line connecting each table, or should I connect every
single
field I want to be participating in the join and have many join lines from
one table to another?

Related fields must be of the same type. You can't create a relation
between a numeric field and a text field, or even between two numeric fields
of a different size (for instance Integer and Long). The one special case
is with AutoNumber fields. These are frequently used as primary keys. The
foreign key field related to an AutoNumber must be a Long Integer field.

In some ways I feel that i am an intermediate user..., but this spanish
issue is causing me to have to relearn everything. I have not had any
success in finding a programmer here in my area to get some local help.
I
have an old access program, 97 which I refer to for its English
instructions,
but I am not sure how much has changed.

Just let me know if I am taking up too much of your time! And thank you
so
much for your help!

You're very welcome! And no, you are not taking up too much time
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Good to hear you've been making some progress

It sounds like your combo box is not bound. Its ControlSource property (for
the room selector) should be the RoomNumber field in your bookings table.
That way, when you close the form and return to that record later, the
RoomNumber in that booking record will automatically select the correct row
in your combo box. More to the point, when you select a room for a new
booking, it will be stored in the RoomNumber field of the booking record.

There should be no reason to reset an autonumber field to 1. The purpose of
an autonumber is to generate a unique value which can be used as a primary
key. When you delete a record with an autonumber key, that value will not
(and should not) be used again. Also, if you start to add a new record and
then cancel it, the autonumber which would have been assigned to the new
record will not be reused.

If you want to create unique numbers without gaps (for example, for your
booking reference number) you could just use a Long integer field and work
out the next available number when you create the field. For example, in
your form's BeforeInsert event:

Me.BookingRef = Nz(DMax("BookingRef", "Bookings"),0) + 1

If you REALLY want to reset an autonumber, it can be done as follows:

Public Function ResetAutonumber( _
sTable As String, _
Optional sField As String, _
Optional lSeed As Long _
) As Long
'sTable = Table containing autonumber field
'sField = Name of the autonumber field
' (default is the first Autonumber field found in the table)
'lSeed = Long integer value you want to use for next AutoNumber
' (default is one more than the current maximum)
Dim cnn As Object 'ADODB.Connection
Dim cat As Object 'ADOX.Catalog
Dim col As Object 'ADOX.Column
Dim tbl As Object 'ADOX.Table
Dim sRemoteTable As String
On Error GoTo ProcErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTable)
If tbl.Type = "LINK" Then
Set cnn = CreateObject("ADODB.Connection")
cnn.OPEN _
CurrentProject.Connection.ConnectionString & ";" & _
"Data Source=" & _
tbl.Properties("Jet OLEDB:Link Datasource")
cat.ActiveConnection = cnn
sRemoteTable = tbl.Properties("Jet OLEDB:Remote Table Name")
Set tbl = cat.Tables(sRemoteTable)
End If
If Len(sField) = 0 Then
For Each col In tbl.Columns
If col.Properties("AutoIncrement") Then
sField = col.NAME
Exit For
End If
Next
If Len(sField) = 0 Then GoTo ProcEnd
Else
Set col = tbl.Columns(sField)
End If
If lSeed = 0 Then
lSeed = Nz(DMax(sField, sTable), 0) + 1
End If
col.Properties("Seed") = lSeed
ResetAutonumber = lSeed
ProcEnd:
On Error Resume Next
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

You can just call it from the immediate window in the VB editor:

?ResetAutoNumber("YourTableName")

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Well by now, I have continued to work on my form, and I am thinking it is
pretty much where I want it. Half the stuff, I don-t know how I have
gotten
to work...but somehow they are! I have a few more issues that really
don't
fall into my original posting subject line, but since you have been so
prompt
and helpful, I wanted to run them by you first.

First of all, for some reason, none of my combo box selections seem to
remain in my form or my tables after I close them and reopen them.

Also, how can I reset the autonumbering to start from 1 again?

Next I will enter about 10 records and start to work on my reports. Stay
tuned!

Thanks!

Hi Andrea

Answers inline...

OK...you said

"To create a query that shows fields from all three tables, open a new
query
in design view and add all your three tables. They should appear in the
window above the query grid, joined by bold lines if you have set up
the
relationships correctly."

First question...why do I need a query...why aren't I just designing a
form
that refers to my three tables. Is it possible that I don't really
understand the concept of queries?

A query is the means by which you can bring together fields from related
tables. You can create a form which has a SQL statement as its record
source (SELECT Field1, Field2 ... from Table1 inner join Table2 on ...)
but
this is still a query. The only difference is that this query is not
saved
so that it can be used for other purposes, such as reports.

Two, I did it anyway, but apparently I don't have my relationships set
up
correctly because the line is not bold and when I save the query and
try
to
reopen it, it says that my field types don't match. (I double checked
several times, and frankly, just don't know where the problem is.)
When I
double click on the line, it does say that i have one to many
relationships,
even though I only connected one field to one field in each table, and
have
only one join line connecting each table, or should I connect every
single
field I want to be participating in the join and have many join lines
from
one table to another?

Related fields must be of the same type. You can't create a relation
between a numeric field and a text field, or even between two numeric
fields
of a different size (for instance Integer and Long). The one special
case
is with AutoNumber fields. These are frequently used as primary keys.
The
foreign key field related to an AutoNumber must be a Long Integer field.

In some ways I feel that i am an intermediate user..., but this spanish
issue is causing me to have to relearn everything. I have not had any
success in finding a programmer here in my area to get some local help.
I
have an old access program, 97 which I refer to for its English
instructions,
but I am not sure how much has changed.

Just let me know if I am taking up too much of your time! And thank
you
so
much for your help!

You're very welcome! And no, you are not taking up too much time
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand