Having separate columns for each employee per tracking record is poor design.
What you should do is record each employee per tracking record in a separate
table related to the tracking table by means of a foreign key column which
references the primary key of the tracking table. That way you can have as
few or as many employees recorded per tracking record as necessary. This
table might have other columns representing each employee's involvement in
the tracking record, i.e. cheque.
The employee details will already be in the Employee table with one row per
employee, so in the new table to record the employee in question you probably
only need a foreign key column such as EmployeeID which references the
primary key of the Employee table. However, you might need to store more
detail than this in the new table. Say an employee changes address and their
record in the Employee table is updated. Do you want all existing tracking
records which include this employee to reflect their new address or to keep
the old one from when the record was created? If the latter then you'd need
to include the address details in the new table as well as the EmployeeID.
This can be automated when you select an employee for a tracking record by
inserting the values for the employee's current address into the relevant
columns in the new table. You'll find something similar done in the Orders
subform of the sample Northwind database which comes with Access, where the
current unit price of a product is used to assign a value to a column in the
Order Details table. Values like DoB, SSN etc will of course only be in the
employee table as these don't change, they are what's known in the jargon as
being 'functionally dependent' solely on the whole of key of the employee
table, which is essentially what, through the process of 'normalization',
determines which columns go in which tables in a relational database so that
redundancy is avoided.
Whichever of the above is appropriate data entry would be via a form based
on the tracking table with a subform embedded in it based on the new table
which records the employees per tracking record. The subform would be linked
to the parent form on the keys columns and each employee would be selected
via a combo box whose RowSource queries the Employee table. The subform
should be in continuous form or datasheet view and each employee would be
selected as a new row in the subform.
For a report you'd join the tracking table to the new table and join that to
the employee table in a query You can then return the relevant address etc
details from the employee table (or form the new table if you've stored the
'historical' address data in that) and include these in the report. The
report can be grouped on each tracking record, with the data from the
tracking table in the group header and the data regarding each employee in
the detail section.
Ken Sheridan
Stafford, England
Gail T. said:
I am creating a database that track employee paid benefits. I have two
tables – Employee Table and a Tracking Table.
The primary match is a single check to many employees.
In the Tracking Table I have employee 1, empoyee2, employee 3, and so-on.
This works find for the input form and keeping the check/employees together.
[If this could be done better, I am very interesting in knowing the how].
The major problem is the report. I need to add the employee’s address, SS#,
DOB, etc to this form. Question – is there a way to create a macro or
formula to pull this information in the report without going through the
exercise of address 1, address 2, and so-on.
