Database Set-Up question

[Guest]

I am setting up an new database, regarding adjustments that my employees give
out.

The fields that I have so far are:
Employee ID
Employee Name
Team Coach
Adjustment amount for Jan, Feb, etc.

The problem that I am having is that an employees team coach can change from
time to time. (i.e. Employee Mark has Team Coach X for Jan, Feb, and March
but then gets Team Coach Z for the rest of the year.) When I run a report
based on Team Coach and how much their team adjusted for the year I wll need
to be able to run one that will show me that Jan - March he was w/Coach X and
then Coach Z.

Thanks in advance for the answers!

 

[Guest]

You will need a table like:
EmployeeID - links to Employee table
TeamID - links to Team table
CoachID - links to Coach table
FromDate
ToDate

Can employees switch from one team to another? Something else to think about

-Dorian

 

[John Vinson]

I am setting up an new database, regarding adjustments that my employees give
out.

The fields that I have so far are:
Employee ID
Employee Name
Team Coach

ok... I'd avoid using spaces in fieldnames though.

Adjustment amount for Jan, Feb, etc.

NOT ok. You're "committing spreadsheet upon a database". Is Jan
January 2006? Is Dec 2005, or 2006? If it's 2005, what will you do in
nine months from now?

If you have a one to many relationship from Employees to Adjustments -
as appears to be the case - you need TWO tables in a one to many
relationship:

Employees
EmployeeID
LastName
FirstName
<other bio data as needed>

Adjustments
EmployeeID
CoachID <I presume coaches are employees too, just link both fields
to Employees>
StartDate
EndDate

If an employee is on three teams over the course of a year, she would
have three records in this table.


John W. Vinson[MVP]