Input info to 2 tables

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

Tlaker

----- Original Message -----
From: "Brett Collings [429338]" <[email protected]>
Newsgroups: microsoft.public.access.gettingstarted
Sent: Thursday, October 21, 2004 4:20 PM
Subject: Re: Input info to 2 tables?

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
Click to expand...

Thanks Al & Brett for the suggestions.....however, what I really want to do,
or find out if it can even be done, is to enter data from a form into 2
separate tables (would it have anything to do with "after update"?). To
review, I have 2 tables, "tblCamper" which holds all the information I need
and a second table "tblarrivedepart" which is a subform on a tab control
located on the only form in my database. I want to be able to change the
arrive and depart fields (using the form) and have the associated
"tblcamper" table reflect the new changes: but I also want those changes
written automatically (as a new entry) to "tblarrivedepart" without any
involvement from the user. This updated information would appear in the
subform as a history of previous visits. I realize that I would have to
re-open the database for the information to be updated, but that's not a
problem. Fact is, I need to do this, if it can be done, for at least 3
different subforms and more if I can't stop having so much fun revising and
reinventing!!! Again, thanks for the help.
 
I want to be able to change the
arrive and depart fields (using the form) and have the associated
"tblcamper" table reflect the new changes:
Click to expand...

OK, I'm only going to give this one more try ... No, you don't want to
do that. Arrivals and departures are the data of tbArriveDepart, not
of the tblCamper record. If you feel you need something else then we
have a data structure problem, not a coding one.

If you do, you are duplicating data in your database and violating
Normalization and data integrity.

If you can't see why today, just believe us that you will one day when
you least expect it and certainly don't want it. You have a need
common to most developers but have the wrong solution that's all.

Present us with the problem and we can certainly help solve that but I
personally will not be comfortable telling you how to do what you want
to do. It comes under the doctor's creed of "do no harm"

Cheers,
Brett
 
Back
Top