Inserting ID Number from Another Table

[J. Trucking]

Hello,

I have two tables. One stores "employee information" and another
stores "time sheet information" for all the employees. The
"Employees" table has a unique identifier for each employee
(EmployeeID). The "Time Sheet" table also has a unique identifier for
each time sheet entry (TimeSheetID). When the data entry person
inputs time sheet information onto the "TimeSheet" table, she selects
the employee from a combo box which combines the first and last name
of the employee from the employee table. What I want to do is
automatically insert the EmployeeID into the Time Sheet table based on
the employee she selects. Can this be done? I havent really used
relationships that much, but is that what I should be doing.

Thanks in advance,

John

 

[Tom Wickerath]

Hi John,

When the data entry person inputs time sheet information onto
the "TimeSheet" table...

Normal users should never access tables and queries directly. You should
create a form with subform instead.

...she selects the employee from a combo box which combines the first
and last name of the employee from the employee table.

This sounds like a Lookup field to me. Lookup fields are evil and generally
cause more trouble than they are worth. I recommend removing the lookup field
from the table. Select the field in table design view. Set the Display
control to Text Box. This is found in the Lookup tab on the lower window in
table design view, when you have the correct field selected. For more
information on the evils of lookup fields, see the 2nd Commandment of Access,
here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Instead, you will use a combo box on the form to allow the user to make
selections from a lookup table.

What I want to do is automatically insert the EmployeeID into the Time Sheet
table based on the employee she selects. Can this be done?

Yes, but it should be done using forms.

I havent really used relationships that much, but is that what I should be doing.

Most definately so! Create a relationship with enforced referential
integrity between the two tables. You will need to have an EmployeeID field
in the TimeSheets table that is the same data type as the EmployeeID field in
the Employees table. If the Employees.EmployeeID field is an Autonumber, then
use a Number / Long Integer in the TimeSheets table. Remove the default value
of zero for this field.

This type of relationship is called a one-to-many (1:M) relationship, ie.
"An employee can have many entries in a timesheet". However, what you might
actually need is a third table, commonly known as a join or linking table, to
allow you to define a many-to-many (M:N) relationship. A M:N relationship is
created by using two 1:M relationships, with the linking or join table having
the foreign key of each 1:M relationship. Translated into English statements,
you would have:

An employee can have many entries in a timesheet
and
A timesheet can include records for many employees

If these two statements correctly describe your data, then you need to
create that third table to implement the M:N relationship. If you stick with
just the 1:M relationship that I discussed first, by adding an EmployeeID
field to the TimeSheet table, then a given timesheet record will apply to one
and only one employee.

If this is sounding rather confusing to you, download and read the first two
papers on database design, written by Michael Hernandez, and available here:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[gllincoln]

Hi,

You have a one to many relationship - you should create that relationship
and save it. This will help the builder wizards and form tools figure out
how to link things.

(In case you don't know), putting the employee's name in the time sheet
table is considered bad form, you don't need it there if you have the
employee ID. You can always look up the name. The underlying reason that you
shouldn't do it is, names do change (women get married, a name gets
mis-spelled and then corrected, etc).

Any changes and corrections made to the employee table will correct the
entries moving forward but they won't fix or correct that stamped in name in
the timesheet table, unless somebody goes to the bother of running an update
query based on the employee table current value - against the old records,
and who has time for that stuff?

That being said, I do that kind of 'against the rules' stuff from time to
time myself, particularly where data is being dragged across a network,
maybe even from a remote location via VPN. If having the name in the record
saves a lot of lookups using a limited bandwidth resource, it may be
worthwhile.

The most obvious way to do what you want (and probably the best) is to
create a main form that contains the employee ID & First/Last Name -
optional other info might be department, supervisor's name, job title with
the employee table as the record source. Leave several inches of blank space
on the bottom of the form.

Create another form for the timesheet, using the timesheet table as the
recordsouce.

Open the main form - in design mode - make sure the wizard tool is on / wand
icon will be depressed if it is.
Drag the timesheet form and drop it on the lower section of the employee
main form.

If you have the relationship defined and saved - Access will make the
necessary parent/child link entries and it will automatically work - when
you select an employee - the timesheet will only show the entries for that
employee (or a blank form to make an entry if none exist). Access will take
care of the 'foreign key field' in the subform for you if you have it set up
correctly. The foreign key field is the employee ID column / field in the
timesheet table.

If the wizard doesn't handle it or you don't have the relationship set up
correctly - then you will have to manually insert the master and child link
fields for the parent (main form) and child form (sub-form) respectively. In
this property sheet the source object is the child form - the timesheet.

The entry place for these items is in the data tab list of the sub-form
properties sheet. Note: not the form properties sheet, the sub-form
properties sheet, an object that serves as the main form's container for the
child form.

Hope this helps...

Gordon