auto fill in

[PR Kid]

I have a table with all my employee's information, name, date of hire, ssn,
etc.
I have a table I want to use that will track there leave of absence, this
table will also show their names, ssn, as well as start and end of LOA's and
reason for loa's. what I want to do is, when I start to fill out the LOA form
and I enter the employee's SSN I want the name field and date of hire to
auto fill in. I would also like to keep a record of how many times an
employee has taken an LOA and the duration of each LOA. I have tried to do
this but I need some help.

 

[Rick B]

You say you will have a table that will track their LOA and also show their
name, SSN, etc. That is incorrect. That table should NOT include those
items. A properly designed relational database includes each piece of
information once and only once. If you use the method you described, and
Mary Jones gets married and changes her name to MAry Smith, you will have a
whole bunch of records to update.

The proper way is to have two related tables. For example...

TblEmployees
EmpNumber
EmpFirstName
EmpLastName
EmpSSN
EmpAddress1
EmpAddress2
EmpCity
EmpActiveY/N
etc.

TblLOA
LOAStartDate
LOAEndDate
EMPNumber
LOAReasonCode
etc.


TBLLOAReasons
LOAReasonCode
ReasonDescription
AllowedDays
etc.


In the above example, you would ONLY store the employee number in the second
table. In your FORMS, REPORTS, and QUERIES, you could include data from
both tables. So, when your users enter a new LOA, they would *SEE* the
employee data on the FORM, but it would not be stored with the LOA table.
Likewise, in the above example, you would store the LOAReasonCode in the LOA
table, but you would not spell out the reason in every record. Again, you
could display the reason text in your forms, but you would not save it.

In short, if you have redundant information, you probably need to normalize
your design.

Hope that helps,