Time Cycle Query?

['Hansel]

I am attempting to build a system to track comp time, vacation time
and sick time. I think my table structure is fine:

[StartDate] {Beginning Date of Time Period From Employee Table}
[InitComp] {Starting Amount of Comp Time From Employee Table}
[InitVac] {Starting Amount of Vacation Time From Employee Table}
[InitSick] {Starting Amount of Sick Time From Employee Table}

[DIn] {Date and Time in from Time Table}
[DOut] {Date and Time Out from Time Table}
[CompTaken] {Amount of Comp Time Taken From Time Table}
[VacTaken] {Amount of Vacation Time Taken From Time Table}
[SickTaken] {Amount of Sick Time Taken From Time Table}

My problem is, this time frame needs to run on a 28 day cycle instead
of a 40 hour week. We're allowed 171 hours of work in a 28 day
period. Anything over 171 hours per 28 days is Comp Time. Anything
up to 171 hours is regular time. I need a way to break the time
frames into 28 day cycles, calculate the total hours, if there is more
than 171 hours in that cycle, add the amount over 171 to the
employee's total comp time. If they take any comp time in that cycle,
it will be subtracted from the total comp time. I can figure out how
to do all the calculations for the totals, I just need to know how to
separate the cycles into 28 day blocks so I can perform the
calculations and keep a running total of the CompTime.

I've used DateAdd("d",[StartDate],28) and that works great for the
first time cycle, but getting it to work for each subsequent cycle in
order to keep a running total has totally blown my mind.

Any assistance with this would be greatly appreciated!

 

[KirSol]

Any assistance with this would be greatly appreciated!

I'm not clear on how your database is set up (exact table names, field
names, relationships), but in any case I suspect you'll need to do
some SQL scripting (i.e. creating temporary tables, scrolling through
recordsets using DAO or ADO, etc.) using VBA to convert the raw
timecard data into ongoing tallies.

Hope that helps a bit.