Input info to 2 tables?

  • Thread starter Thread starter Tlaker
  • Start date Start date
T

Tlaker

I manage a campground and have decided to transfer my files from MS Works
database to Access 2k. I have a flat database now with, among other fields,
“Name”, “Arrival” and “Departure” written to the table. I’d like to have
these fields written to a subform, “History” that would show at a glance the
dates of previous arrivals of each separate camper. Because my other
workers are not computer literate, I need to make the input as simple as
possible. Is there a way of entering data on the main form, replacing what
was there with new information and at the same time having the same
information added to a subform as a new record unique to “Name”, keeping all
the old information? Thanks for any help you can offer this newbie.
 
Tlaker,
Your main form should be based on a table that contains the Customer
information, and your subform should be based on a table that contains your
Arrival Departure information. For example tblCampers and tblVisits.
Each Customer in tblCampers will have a unique identifying number, (a key
field) and will be "related to" the tblVisits by that number. Ex. a
CamperID.

This is a normal "one to many" relationship. One Camper, Many visits.

Only Camper information is entered on the main form (one time only...
except for any occasional changes in that information), and after each new
visit, you'll just add another "visit" record to the multiple record subform
for that camper.

Check out "one to many realationships" in Help, and read all about how to
set up your key field, and table fields.
hth
Al Camp
 
Al's reply is excellent ... do it :)

The tables you will likely end up with are
tblCustomer
------------
CustomerID autonumber Primary Key
... all the one-time-only customer contact and billing information

tblSites
-------
SiteID Autonumber Primary Key
SiteNumber Text (You only use number type when
you are going to do calculations
with the data, in this case you're
not)
SiteName Text
... and other fields about the site, like if the caravan is owned
and rented on behalf in which case you might include the owner.

tblCustVisit
----------
CustVisitID Autonumber Primary Key
dteVisitBooked Date/Time You can set the default for
this field as =Now().
This will timestamp
(Date and Time) the exact
moment when you took the booking
dteVisitIn Date/Time Same default for when they arrive
dteVisitOut Date/Time ditto
SiteID Number Foreign Key (This allows you
to just select the site
from a drop down list
that looks in
[tblSite].[SiteID] for
the list - look up
"Foreign Key" in Help)
CustomerID Number Foreign Key (This foreign key will be
filled in automatically
using a Form/Subform
relationship)

To present this information to your users and in fact to synchronise
the tables, you will use a Main Form ("frmCustomers") and a SubForm
("sfmCustVisits")

When those forms are set up, every time you make a new entry in the
subform about a visit, Access will add the CustomerID to that entry in
the visits table because of the customer record being displayed on the
Main Form (frmCustomers)

It might all sound a bit confusing now, but it's a piece of cake
really ... and we're your 24/7 free Help Desk :)

Brett


I manage a campground and have decided to transfer my files from MS Works
database to Access 2k. I have a flat database now with, among other fields,
“Name”, “Arrival” and “Departure” written to the table. I’d like to have
these fields written to a subform, “History” that would show at a glance the
dates of previous arrivals of each separate camper. Because my other
workers are not computer literate, I need to make the input as simple as
possible. Is there a way of entering data on the main form, replacing what
was there with new information and at the same time having the same
information added to a subform as a new record unique to “Name”, keeping all
the old information? Thanks for any help you can offer this newbie.
Click to expand...

Cheers,
Brett
 
Back
Top