Getting Information!!Help!!

N

Nikki

I have a table (basic) with some basic information like
Employee Number, Name, Address, and phone number. I then
have another table (benefits table) that needs this same
information(employee #, name,address, and Phone #)
whenever a new record is entered. Now I have to type it
in everytime. I want to be able to have those fields
automatically filled out using the basic table. Then I
want to add my data to the benefits table and have that
all saved in the benefits table. Nothing is to change in
the basic table. The benefits table may have 20 entries
for the same employee. Please help!! This seems so easy
yet nothing I do works. I know I am just missing an
obvious step. If you need any clarification please ask!
 
I

Immanuel Sibero

Nikki,

You need to read up on some basic relational database theory. Access is a
database software which implements this theory. Therefore, without some
basic training in relational database design and normalization, Access is
useless. As a matter of fact, it may even backfire on you to the extent that
you'd be better off using Excel.

If you're content on building the database yourself, please browse through
the tabledesign forum. You will find lots of information on relational
database design that will help you avoid mistakes. You will also find
references on what books, or web sites to visit in your quest for knowledge.

For example, from your post, applying the relational database theory would
be to set up 3 tables:

- tblEmployee
- tblBenefit
- tblEmployeeBenefit (junction table)

The assumption here is there is a many-to-many relationship between employee
and benefit. This design allows you to enter employee information such as
address, phone, name, etc etc. only ONCE. It also allows you to enter
benefit information only ONCE.


HTH,
Immanuel Sibero
 
N

Nikki

I have used Access - can you just help a little further
please!! I see what you are saying about three tables
that makes sense. How do you get the third table to pull
information from the other two? Can you just expand a
little more on the explanation a little?
 
A

Al

Nikki,

Have you tried using a main form and a subform layout.

The Employee Information would be your main form and the
link to your subforms (Benefits, etc.) would be subforms
based on employee id or whatever the identifier you
indicated.

Al
 
I

Immanuel Sibero

Nikki,


that makes sense. How do you get the third table to pull
information from the other two? Can you just expand a
Click to expand...

tblEmployee contains primary key EmployeeID and all information specific to
employee.
- EmployeeID (Primary Key)
- FirstName
- LastName
- Address
- City
- etc... etc..

tblBenefit contains primary key BenefitID and all information specific to
the different type of benefits you have.
- BenefitID (Primary Key)
- Description
- Taxable
- Administrator
- etc.. etc...

tblEmployeeBenefit contains foreign keys (EmployeeID and BenefitID).
- EmployeeID (Foreign Key)
- BenefitID (Foreign Key)
- StartDate
- EndDate
- etc.. etc..


that makes sense. How do you get the third table to pull
information from the other two?
Click to expand...

You do this by setting up the primary keys from the other two tables into
the third table (i.e. they're referred to as foreign keys). The third table
is frequently referred to as a junction table. Expanding on a little bit,
this design allows you to set up all your employees and all the different
benefits that your company offers. Once they (i.e. employees and benefits)
are all set up, you can then record in the database an event where you award
a certain benefit to a certain employee. You do this by inserting a record
into tblEmployeeBenefit.

For example, suppose you have an employee John Doe with EmployeeID of 1, and
you have a benefit 401(k) with BenefitID of 1. To record the awarding of
401(k) benefit to John Doe, you would insert a record into
tblEmployeeBenefit as follows:
1,1,08/05/04,

Suppose a second employee Jane Doe with EmployeeID of 2 gets the benefit,
you would insert a record in tblEmployeeBenefit as follows:
2,1,08/05/04,

One of the ways to do this in Access is through the use of Main form/Subform
layout as another poster has suggested. Access Help gives prette good
information on the use of subform.


Notice that you dont have to reenter the addresses of the employees and all
information related to the 401(k) benefit. You enter this information only
once when you first set them up.


HTH,
Immanuel Sibero
 
Top