Relationships

[Guest]

I have several tables in my access database. I built them in Access 2003. I
want to be able to add data, like someones name, and have it automatically be
entered into the other tables. The tables do have a relationship connected by
name. Any idea of how to do this? Thanks

 

[John Vinson]

I have several tables in my access database. I built them in Access 2003. I
want to be able to add data, like someones name, and have it automatically be
entered into the other tables. The tables do have a relationship connected by
name. Any idea of how to do this? Thanks

Well...

DON'T.

You're misunderstanding how relationships work. Relational databases
use the "Grandmother's Pantry Principle": "a place - ONE place! - for
everything, everything in its place".

You would store the name once, and once only, in the People table (or
other appropriate table). If you need to see that name in conjunction
with data from other tables, you can use a Query joining the two
tables, joining the unique ID of the table containing names to a
"foreign key" field in the other table (these fields will generally be
specified in a Relationship). Storing data redundantly in multiple
tables is simply NOT a good thing to do!

Further, a name is NOT a good choice of a Primary Key or a linking
field. A good primary key *must* be unique (occurring once and once
only in the table); it should also be stable (not changing over time)
and preferably short (so searches run faster). Names fail on all three
counts.

You might want to look at some of the tutorials on these websites,
particularly the Database Design 101 links on Jeff's site:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

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


John W. Vinson[MVP]

 

[Joan Wild]

I have several tables in my access database. I built them in Access 2003. I
want to be able to add data, like someones name, and have it automatically
be
entered into the other tables.

You usually wouldn't want to do this; rather you would only enter the
information in the related table when the information becomes available For
example, if you have a table of Customers and a table of Orders. When you
create a customer, you wouldn't want to automatically create a dummy record
in Orders with the Customer ID. You'd wait until you actually have an order
before entering it in the Orders table.

Even with this setup, you'd likely have a form for the Customers, and use a
subform for the Orders - the Northwind sample database that comes with
Access demonstrates this.

The tables do have a relationship connected by
name.

That may not be the best choice to connect the two tables. A name is
something that isn't unique, and you won't be able to distinguish, for
example, the orders for Bill Smith vs. the orders for the other Bill Smith.

 

[Guest]

When you

create a customer, you wouldn't want to automatically create a dummy record
in Orders with the Customer ID. You'd wait until you actually have an order
before entering it in the Orders table.

Actually that is excatly what I want. I am tracking employees and all their
certifications, so I am not "waiting" for them to recieve the cert, they
should already have it. And since we often have new employees, I want to be
able to only add them to one table and not have to do it to all three (or
whatever number I have). Thanks.

 

[Joan Wild]

OK then go with the form/subform approach. You enter an employee, and if
they have the cert you can enter that on the subform. If they don't, then
you don't enter anything in the subform. You still have the employee
information. When they *do* get the cert, you open your form, find the
employee record, and fill in the subform with the cert information.