Any ideas?

Hello,

I am stuck on a design issue and would like some suggestions please.

I have an employee db. In this db is an employee table that has their
current info... salary, bonuses etc. What would you suggest is a good way to
get a historical system going. For example week 10, person X gets an annual
bonus. Week 19 person Y gets a travel expense.

Any ideas would be appreciated.

Joe C
Big Papi for president

Two tables, your current employee table with basic info - name, address,
phone, DOB, Date of Hire, etc - and an EmployeeId. and a second table to hold
the historical data that looks something like this:
EmployeeID - FK
Description - salary, bonus, etc.
Amount
EventDate

This table would have a many-to-one relationship with the employee table on
the EmployeeID.

Your data entry form would be a Single based on the employee table and you
would have a subform in that form that is Continuous and based on the new
table. These two tables would be linked by the EmployeeID.
Whenever you entered data in the subform, the EmployeeID (which doesn't need
to be displayed on the subform, will be entered in your new table.
Hope this helps,
Bob

Joe C wrote:
Hello,

I am stuck on a design issue and would like some suggestions please.

I have an employee db. In this db is an employee table that has their
current info... salary, bonuses etc. What would you suggest is a good way to
get a historical system going. For example week 10, person X gets an annual
bonus. Week 19 person Y gets a travel expense.

Any ideas would be appreciated.

Joe C
Big Papi for president

--
Message posted via http://www.accessmonster.com

Jahoobob,

I like it.

I have a large employee base whose salary will not change week to week.. Is
there an easy way to populate the second table?

JC

Papi


"jahoobob via AccessMonster.com" wrote:

Two tables, your current employee table with basic info - name, address,
phone, DOB, Date of Hire, etc - and an EmployeeId. and a second table to hold
the historical data that looks something like this:
EmployeeID - FK
Description - salary, bonus, etc.
Amount
EventDate

This table would have a many-to-one relationship with the employee table on
the EmployeeID.

Your data entry form would be a Single based on the employee table and you
would have a subform in that form that is Continuous and based on the new
table. These two tables would be linked by the EmployeeID.
Whenever you entered data in the subform, the EmployeeID (which doesn't need
to be displayed on the subform, will be entered in your new table.
Hope this helps,
Bob

Joe C wrote:
Hello,

I am stuck on a design issue and would like some suggestions please.

I have an employee db. In this db is an employee table that has their
current info... salary, bonuses etc. What would you suggest is a good way to
get a historical system going. For example week 10, person X gets an annual
bonus. Week 19 person Y gets a travel expense.

Any ideas would be appreciated.

Joe C
Big Papi for president

--
Message posted via http://www.accessmonster.com

What are your current table's fields that pertain to the data (date, amount,
description, employee identification, etc.) you want to keep in the history
file? Give me this and I can better help you.


Joe C wrote:
Jahoobob,

I like it.

I have a large employee base whose salary will not change week to week.. Is
there an easy way to populate the second table?

JC

Papi

Two tables, your current employee table with basic info - name, address,
phone, DOB, Date of Hire, etc - and an EmployeeId. and a second table to hold
[quoted text clipped - 28 lines]
Joe C
Big Papi for president

--
Message posted via http://www.accessmonster.com

Joe C wrote:
the historical data [table] looks something like this:
EmployeeID - FK
Description - salary, bonus, etc.
Amount
EventDate

have a large employee base whose salary will not change week to week

I recommend you store salary history (e.g. in a continuum) separate to
one-off events such a bonus; store salary on a weekly basis and you'll
do so redundantly.

Be warned that such tables, called valid-time state tables in the
literature, are very tricky to get right; even writing a primary key
constraint is complex. Here's an example I posted earlier:

http://groups.google.com/group/micro...c3f233ba3336cc

Jamie.

--