Adding SubForm to Existing Form

[JWeaver]

I have a Form that is based on a Table that currently contains a lot of
information that repeats in different records. I want to split this Table
into multiple Tables so that one Table contains just the information
pertaining to each employee (i.e., ID, Last Name, First Name, Emp #, and Pay
Rate) and another Table to gather the payroll specific information (i.e.,
Payroll Date, From Date, To Date, Hours, Miles, and Mileage Rate). I would
like the information in the second table to become a Subform in the Form I
already have.

How can I split this table into 2 tables without losing anything regarding
which payroll data pertains to which employee?

Is it possible to change the Form I have so that a Subform can be added with
all of the payroll information for a particular employee?

Any help you can give me would be appreciated.

 

[Clifford Bass]

Hi,

Good for you! You are desiring to head in the right direction. Before
you do anything, make sure to make a backup of the database.

To split the table, create the two new tables that you described. Make
the Emp # in your new employee table a uniquely indexed field. You may
actually want a third table that documents the pay rate (Emp #, start date,
end date, rate). I presume that your Emp # is how you uniquely identify the
employees. Since you may have different names (John J. Jones / John James
Jones) and addresses (123 Main St. / 123 Main Street) for each employee one
issue you will need to figure out is how to identify the most correct
instance of each employee. One way would be to create another table. One
that is a temporary work table that will contain all distinct variations of
the employee data and that will allow for multiple instances of the same Emp
#. Use an append query that uses a "select distinct field1, field2, ... from
originaltable" to populate the work table. Search Access's help for "append
query, including the quotes, if you are not familiar with them. To make it
do the "select distinct..." right click in a blank space in the top of the
query designer window and choose the Properties item. Change the Unique
Values item to Yes. Once you have populated the work table someone will need
to go through it, editing the data so that there is only one, correct
instance of each employee. Then use another append query to copy the
information from the work table into the new main employee table. Next, use
another append query to populate the payroll table. You may have to do the
process more than once in order to get it right. Check the results each time
to make sure. Once it has worked correctly, you can remove the old table and
the work table.

Next you will need to edit your current form. It may make sense to
make a copy of it. Then modify the original, removing the payroll
information. Modify the copy, removing the employee-specific information.
Then back in the modified original make sure the control wizard is turned on
(it is the button with the slanted magic wand with dots/stars falling from
the top). Then choose the subform control item to add the modified copy,
following the prompts. You can search for "subform" in help for more
information.

Hope that helps,

Clifford Bass