Table setup help

[Zonie32]

Using winxp, access 2003.

I am tracking the dates that our employees carpool versus the dates that our state says are HPA days (high pollution).

If an employee happens to carpool the same day as one of the HPA days, we enter them into a prize drawing.

So the tables I have so far are:

tblEmployees
EmpID, FName, LName, Carpool_Date

tblHPADays
HPA_Date

Some employees only carpool a few days of the month, some do the whole month. So if I were to enter the carpool date into the tblEmployees table for someone who carpooled say 3 days that month, I would have to enter them 3 times with the different dates. Right?

But if someone carpooled every day of the month, say all of September, would I have to enter that employee with the dates 20 times, 1 entry for each day of the week? OR is there a better way to handle entering the carpool dates?

 

[Lilliabeth]

Carpool Date should not be in the Employees table. The fields in the Employees should be limited to facts about employees - facts of which there is never more than one for an employee. An employee can have only one first name and one last name, for example.

YOu need a seperate table - maybe with these fields:
Employee_ID (to join to tblEmployees), and Date

 

[Zonie32]

thanks for the reply, but in the new table you proposed with EmpId and Date, how would I know EmpID 1 gets this carpool date and EmpID 10 gets that date? We have 400 employees.

can anyone suggest tables set up for me. thanks a lot.

 

[johnherman]

Employee Table
EmployeeID (PK)
EmployeeName
EmployeeDept
EmployeeCarpoolFrequency (see below)
etc

HighPollutionTable
HPDate (PK)

EmployeeCarpoolTable
EmployeeID (FK)
CarpoolDate (FK)

Domain for EmployeeCarpoolFrequency could be (D)Daily, (F)Fridays, (A) Ad-hoc/Random, etc.

Then you could have a process/program which would automatically populate the EmployeeCarpoolTable for those who carpool Daily or Fridays. The Ad-Hoc (random) ones would still need manual attention, as would scheduled carpools which did not occur.

-------------------------
The trouble with doing something right the first time is than nobody appreciates how difficult it was - Steven Wright