This is a follow on from the following question: http://www.experts-exchange.com/questions/28706118/Cross-Tab-on-flat-record.html#a40932777
I have a requirement to track employee time by each job they perform.
* There could be anything from 1 to say 10 shift segments in any shift.
* Each segment needs a date, start time and end time
* I need to be able to compute and record BaseHours (first 7.6 hours Monday to Friday or zero hours on weekends); Time and a half (first two hours after the BaseHours limit is reached) and Double time for excess hours. If an employee does less than BaseHours on any day, the BaseHours limit for the following day is increased by the shortfall. That part's been relatively easy and is working fine.
* Employees may also receive allowances. They are entitled to one unit of the allowances per day so if an employee only does say 3 jobs during a day then the first segment gets allocated 0.34 units of allowance and the remaining two segments get 0.33 units.
* Alternatively employees may take a shift as sick leave or annual leave.
The question arises about how to best store the information. At present I am using one field for each component: BaseHours THalfHours DTimeHours ALHours LoadingHours SickLeave OTJAllow OwnCarAllow TravelAllow CFwdShortfall. As each new record is added, or record is edited I simply loop through the records for the day and recalculate the values.
Now I need to introduce a summary on the data entry sheet to reduce input errors. The summary needs to look something like the following.
The problem is that creating a query that will accomplish this means creating a UNION query with multiple Select statements and then I need to use that as a basis for a crosstab query to come up with the required output - very cumbersome.
The alternative is to create the table structure that Dale recommended in the related question, i.e. output each component (BaseHours; THalfHours; etc) as separate records in a linked table but this means that every time a new shift segment is added or an existing one is edited, the only safe way to recreate the data would be to delete all the records for that day and add replacements to the table. I am not sure if the overhead of adding and deleting records like this is the ideal solutions to this problem.
PS Sorry for the novel.
Fri Sat Sun Mon Tue Wed Thu