I created this database to help the personnel administration
department perform its duties more efficiently. First of all,
the only thing it really deals with is personnel. No products,
or sales figures or anything like that. Each record in the
database represents an employee, and the only primary
keys are SSNs.
Ok...
One of the things the database keeps track of is dependant
information. Currently, my primary table is populated with
these fields(there are more fields than this. This is just an
example):
So would this kind of setup warrant a normalization process
to separate the personal fields and dependant fields?
Absolutely.
A dependent IS A PERSON, and entity in his or her own right.
Rather than one FIELD - or group of fields - per dependent, you should
have *another table*, with one RECORD per dependent. This table would
have the employee's SSN as a foreign key to the Employee table. This
would let you put as many dependents as needed in the table, just by
adding more records; give you only one field to search to (say) find
out which employees have children named Betty; many, many advantages.
The
table also has fields for categories like security, vehicles,
training received, issued equipment, etc. For another
example, we have about a dozen or so company owned
vehicles that must be checked out each morning to people
who are authorized to drive them, so the fields in the
database look like:
Vehicle1(yes/no)
Vehicle2(yes/no)
Vehicle3(yes/no)
and so on...
Again: repeating fields are bad news; storing data in fieldnames is
even worse news. What do you do when you buy three more vehicles?
Change the structure of your table, all your forms, all your queries,
all your reports that involve vehicles? Ouch!
Instead, you need THREE tables:
Employees
SSN <primary key>
LastName
FirstName
<other biographical data>
Vehicles
VIN <primary key>
Make
Model
<other fields about the vehicle>
VehicleCheckout
SSN <link to Employees, who checked it out>
VIN <link to Vehicles, what jalopy the got stuck with>
DateOut Date/Time <when they checked it out>
DateIn Date/Time <when they returned it>
I imagine most of the other fields are similar: issued equipment is
clearly a one-to-many; likewise training; etc.
You mentioned append queries to acheive this normalization.
I have looked at the links you provided and am still perusing
through, but so far I have again found nothing but lessons
on the CONCEPT of normalization, not step-by-step how-to
instructions for Access. I already know what fields need their
own tables, I just need a way to separate the table without
having to manually reenter everything.
I can't see your actual table, and you may not have the information
you need to do this totally: if the Employee record just has a yes/no
field for Vehicle3, it appears that you're not keeping any record of
WHEN the vehicle was checked out, just that it's out right at the
moment...? In that case, historical data simply doesn't exist so you
*can't* transfer it.
You didn't post information about other areas, but if you would care
to do so, I could suggest how to write a query to migrate the data.
The exact query will depend on your current and new table structure,
it's hard to write an example that won't just be confusing!
John W. Vinson [MVP]
