pls help with my table

[jiji]

I will explain to you in details my problem.
i have 2 tables.
first table called prsonnel info: the fields are: member ID(type text) it is
a primary key also, name adress, phone, ..

my second table called affiliation.: member ID, affiliation, name,do jan no,
level... Now my problem is the following: i want to be able to enter info in
the first table and automatically this info would go to the affilaiton
table. ex: ID number=123 name=paul belanger. so this info should show on my
affiliation table without having to enter the info twice. by the way my
relationship i think should be one to one since a member cannot have the
same ID twice neither in the personal info nor in the affiliation table.
what do you think?thank you so much for any help
jiji

 

[Rick B]

You don't store name in both tables. That defeats the purpose of a
relational database. You only store the name in the personnel table. You
store the member number in both tables so that you can retrieve the data
from both tables. When you want to print or display data from the
affiliation table and include the person's name, then you simply include
both tables in your query and make sure they are linked (or related) on the
member ID field. Then you can pull fields from both tables. You could the
affiliation from the affiliation table and the name from the personnel
table.

This is a one-to-many relationship where you'd have one record in the
personell table for each person and one or more related records in the
affiliation table for each person. You don't want to store the name in both
tables because it would make it very difficult to make changes. What if
Mary Smith gets married and changes her name to Mary Jones. What if she has
7 records in the affiliation table and one in the personell table? Do you
want to correct her name 8 times (your current setup) or one time (the
normalized design indicated in this post)?

 

[Kim K.]

Rick, Guy down the hall is asking me the same question. He is CREATING a new
table, not running the report. Can you use a query on the form to
autopopulate a record with data in fields that are stored in another table?
We a big table of employee ID's, with all the other data, and using the ID as
the key, how do I pull out the first name, last name, location, etc. to store
in the new table. Since the entire ID table is huge, and we don't need
automatic updating, I really don't need the tables linked. I am just looking
for a faster way to create the new table. This seems like Access 101, but
it's still frustrating us.

 

[Rick B]

If you have the data in an existing table, you should just use that table.
Then when you have to change data, you (or other departments) just do it in
one place.

Now, if you are saying this is an old outdated table that no one maintains,
and you'd like to pull some data out of it to create a new database (or
table) then you could do this several ways. A couple that will most likely
work for you are:

1) Just copy the table to a new one and delete any fields you don't need.

2) Make a new table and then write an append query to copy any records that
meet your criteria into the newly created table.

Generally speaking, if possible, I would always link to an existing employee
table. If your personnel department maintains the data in a table that you
can gain access to, then you would have the data and someone else would have
the task of making sure it was always accurate.

 

[Bob Miller]

For data entry, use a form based on the prsonnel table and continuous
subform based on the affiliation table linked by the member ID. You
can enter the member info and then put as many affiliations as needed.