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 do***ents 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 tem****ary 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
"JWeaver" wrote:
> 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.
>
> --
> JWeaver
|
