Updating Multiple Tables Automatically.

Hey there,

I'm building a new database for the company I work with and need to know
how to update several Tables automatically whenever I input data into the
form(s). Only three main items will be in all of the Tables to link the data
from the others together--the ID Number, Last Name, First Name, all other
data in the various Tables will be unique (one to hold contact information,
another to hold medical information). What is the correct way to do this?
Should they be on several Tables or just one main Table? I'm also planning to
have Forms with info from the different Tables together. Any help would
appreciated!

Thanks.

Hi,

if the information is one to one, you can put it in the same table.
Example: 1 person can only have 1contact address.
if the information is one to many, you should put them in separate tables.
Example: 1 person can have 2 or more contact addresses.
If you put the information in separate tables linked by a one to many
relationship you can show them in a form (data from the 1 side) with a
subform (data from the many side).

Hope this helps a bit
--
Kind regards
Noëlla


"OptimusTyme" wrote:

Hey there,

I'm building a new database for the company I work with and need to know
how to update several Tables automatically whenever I input data into the
form(s). Only three main items will be in all of the Tables to link the data
from the others together--the ID Number, Last Name, First Name, all other
data in the various Tables will be unique (one to hold contact information,
another to hold medical information). What is the correct way to do this?
Should they be on several Tables or just one main Table? I'm also planning to
have Forms with info from the different Tables together. Any help would
appreciated!

Thanks.

I'm not clear on what information you are trying to update.

If you have a main table (apparently identifying individuals) with PersonID,
LName and FName, then you would NOT need to copy that information into each
of the other related tables ... you would only need to use the PersonID (as
a foreign key) in those other tables.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"OptimusTyme" u54993@uwe wrote in message news:9c8d26d6c3366@uwe...
Hey there,

I'm building a new database for the company I work with and need to know
how to update several Tables automatically whenever I input data into the
form(s). Only three main items will be in all of the Tables to link the
data
from the others together--the ID Number, Last Name, First Name, all other
data in the various Tables will be unique (one to hold contact
information,
another to hold medical information). What is the correct way to do this?
Should they be on several Tables or just one main Table? I'm also planning
to
have Forms with info from the different Tables together. Any help would
appreciated!

Thanks.

On Wed, 23 Sep 2009 14:14:50 GMT, "OptimusTyme" u54993@uwe wrote:

Hey there,

I'm building a new database for the company I work with and need to know
how to update several Tables automatically whenever I input data into the
form(s). Only three main items will be in all of the Tables to link the data
from the others together--the ID Number, Last Name, First Name, all other
data in the various Tables will be unique (one to hold contact information,
another to hold medical information). What is the correct way to do this?
Should they be on several Tables or just one main Table? I'm also planning to
have Forms with info from the different Tables together. Any help would
appreciated!

Thanks.

STOP.

You seem to have a fundamental misconception about how tables and
relationships work.

The name information should exist once, and only once, in only one table.
Other related tables should have just an ID as a link to the names table.

Normally you will have a one table related one-to-many to several other
tables. The perferred tool for managing this situation is to use a Form for
the main table with Subforms for the related tables. In your example you talk
about contact information and medical information; it's not clear just what
you mean though. If the "contact information" is single-valued information
about the person (their home address, home phone, etc.) then it should just be
in fields in the main table of people; if instead you're referring to this
person's "contacts" (other people) then you need a one-to-many relationship.
Medical information is probably a many to many relationship (each Person has
zero, one or more MedicalConditions, each MedicalCondition can apply to zero,
one or more Persons); if so you need three tables: a table of Conditions; the
table of people; and a third table with fields for the person's ID and the
condition's unique ID.

Here are some resources to get you started. Crystal's Database Design 101
chapter in her tutorial might be worth a look to start with.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]

We don't want to use subforms where the date has to be input twice. I've
realized that Relationships aren't the way to go, but I thought there was
another way to link them. So I'm going to try to use just one main Table to
build the multiple forms off of. I'm just worried that later on that the laws
and regulations will change and they'll need to input more information which
will eventually reach over the 255 limit that Tables are suppose to have, (or
am I mistaken on that as well?)

--
Message posted via http://www.accessmonster.com

If you are even approaching the 255 field limit your design is almost surely
unworkable.

Why would you have to input the dates twice? How have you come to the
realization that "relationships aren't the way to go"? If that is how you
choose to approach this you would probably do better to use a tool such as
Excel that does not use relationships.

OptimusTyme wrote:
We don't want to use subforms where the date has to be input twice. I've
realized that Relationships aren't the way to go, but I thought there was
another way to link them. So I'm going to try to use just one main Table to
build the multiple forms off of. I'm just worried that later on that the laws
and regulations will change and they'll need to input more information which
will eventually reach over the 255 limit that Tables are suppose to have, (or
am I mistaken on that as well?)

--
Message posted via http://www.accessmonster.com

Perhaps reading the below will assist you in setting up a Relational
Database...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

http://www.databasedev.co.uk/table-of-contents.html

http://www.databasedev.co.uk/data_models.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:9c9973bb38cf2@uwe...
If you are even approaching the 255 field limit your design is almost
surely
unworkable.

Why would you have to input the dates twice? How have you come to the
realization that "relationships aren't the way to go"? If that is how you
choose to approach this you would probably do better to use a tool such as
Excel that does not use relationships.

OptimusTyme wrote:
We don't want to use subforms where the date has to be input twice. I've
realized that Relationships aren't the way to go, but I thought there was
another way to link them. So I'm going to try to use just one main Table
to
build the multiple forms off of. I'm just worried that later on that the
laws
and regulations will change and they'll need to input more information
which
will eventually reach over the 255 limit that Tables are suppose to have,
(or
am I mistaken on that as well?)

--
Message posted via http://www.accessmonster.com

On Thu, 24 Sep 2009 12:43:18 GMT, "OptimusTyme via AccessMonster.com"
u54993@uwe wrote:

We don't want to use subforms where the date has to be input twice.

A subform is an excellent tool to ensure that data needs to be entered only
ONCE. If your subform is making you enter data twice... fix the subform, or
the tables, or the relationship!

I've
realized that Relationships aren't the way to go, but I thought there was
another way to link them.

I'll have to disagree. An Access database without relationships is incorrectly
designed. Properly related tables are ABSOLUTELY ESSENTIAL to any productive
use of the program.

So I'm going to try to use just one main Table to
build the multiple forms off of.

And that's a serious misuse of the program. Access *is not a spreadsheet*, and
storing all your data in one table is simply incorrect design and misses all
of the power and capability of Access.

I'm just worried that later on that the laws
and regulations will change and they'll need to input more information which
will eventually reach over the 255 limit that Tables are suppose to have, (or
am I mistaken on that as well?)

There is a 255 field limit on a table... but you wouldn't want to build One
Great Master Table in any case. Could you perhaps post some examples of the
kinds of data you need to capture? I'm *absolutely certain* that a bunch of
one-to-many and/or many-to-many relationships can be used instead.
--

John W. Vinson [MVP]