Id' suggest two tables. One with Employee name, any info the employee that
needs to be stored in this system, plus a employee number ("EmployeeNum") or
other short unique identifier.
Then a table with a record for each instance of any employee working.
Three fields:
EmployeeNum of the person who did it.
Amont of time in this instance of working
Location of the work
I see some problems with this. You could only ever enter the number of
days an employee had worked at a particular location after they had
ceased working there; the alternative would be to increment all
durations by one every day (i.e. simulate the ticking of the clock),
in which case you wouldn't be able to record durations for locations
an employee formerly worked -- catch 22. Also, I get the impression
that the OP wants to be able to model durations for the same employee
in the same location at different points in time.
Rather, I would expect the required table to be a valid-time state
table, using start- and end date pairs on each row to model periods of
time (see
http://en.wikipedia.org/wiki/Temporal_database). The NULL
value for end date could be used to signify the current state (i.e. no
end date means the employee is currently working at that location) and
queries could use the DATEDIFF expression (or perhaps a Calendar
table) to calculate the duration between start date and end date,
substituting NULL end date values with the current timestamp NOW().
I don't think the OP has supplied enough in the way of detail to
attempt a proposal e.g. can an employee be considered as working at
multiple locations at the same time? how many hours/minute/seconds
must an employee work at a location for it to be considered 'one day'?
closed-closed or closed-open representation of periods? smallest
considered time granule? etc
One thing to note, however, is that valid-time state tables often
require sequenced constraints which need to be implemented in Jet
using CHECK constraints, which can be tricky to get working correctly.
I've posted on this topic on a number of occasions... Here's one from
the archives which uses a similar scenario of employees' salary
history:
http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc
Jamie.
--
