Duration calculations and a default start date

[Duncan Edment]

I have a TimeSheet database, into which employees record
their daily working hours.

For this current year, 2004, data entry has started on 5th
January, 2004 and all calculations are performed based on
this start date. By 'calculations', I am referring to
figures showing how many hours up or down an employee is.
For example, we each have to work a 37 hour week. So, if
I only work 35 hours in a specific week, my "Weekly + / -"
figure would show "-02:00".

Over the course of a four week period, I should have
worked 37 x 4 = 148 hours. However, if I have worked 150
hours, my "Cumulative + / -" figure would show "02:00".

So, what's my problem?

Well, if a new team member joins us, I am at present
having to manually enter information for that new
employee, for each week starting from 5th January, 2004
until their start date. This data consists of an entry of
07:24 for each day of a working week, using a dummy job
code. What I'd like to know is, is there an easier way of
achieving this? Is there some way I can code the database
to "assume" a value of 07:24 for every day since the 5th
January, until the employee starts, and then continue with
any data that the employee has entered? Or, is there some
other way in which the same result can be achieved,
without relying on previously entered data?

Many thanks & regards

Duncan

 

[Jeff Boyce]

Duncan

I'm not entirely clear on what data you record, or how you do your
calculation, but I suspect you could get away with not entering "dummy" data
for employees hired in after your "start calculations on this date".

Have you looked into using the Nz() function in your calculations, to
handle "no data" situations?

Have you looked into "directional" joins (LEFT, RIGHT) to collect all
Employees, whether or not they have (non-zero) records for a time period?
(this would depend on how your data is structured)

Have you looked into using a cross-tab query?