I have a payroll file that contain record of hours for different code and schedule hours . For one day a employee may work to multiply codes but total schedule hours is always the same I need to total hours for different code which in not a problem; but to total schedule hours just the once per day.
example
Hours Code schedule hour date
8225 1.5 12 Jan 01
8226 8.5 12 Jan 01
8227 2.0 12 Jan 01
I need the total for schedule hours to be just 12 and not 36
I'd change the data model to avoid the denormalized data that's causing the problem. Not sure exactly what your data looks like, but maybe something like this:
The ID here has no meaning except to tie the two tables together. All codes are still associated with the 12 hours on Jan 1. If you sum up hours separately for each code, you'll get 12 for each. But if you total across codes, you'll still get 12.
I have a schedule file and a transaction file. I can't link the schedule to the transaction because not all hours are schedule and if I link the transaction to schedule I get the original problem. The transaction fine has the schedule hours but like I said for every line. I am not sure how to create the ID to tie the two tabled together without using the date and employee.
You could create an ID with something as simple as the recno() function. As for all of your transaction vs. schedule stuff, sorry, but I'm not following. If you want to post the relevant tables and their fields and a small bit of sample data, maybe I could help.
Ah, OK, I think I misinterpreted it the first time around, then. If I'm undersanding it correctly now, then you don't need a new ID. You can just split this table into two pieces, a Work table and a Schedule table. Both are keyed by employee and date, but the work table is ALSO keyed by code. Though I'm a little worried by you saying "if I link the transaction to schedule I get the original problem". Since it seems to me that what I've just done is link a transaction (work) table to a schedule table (by synthetic key), that you might get "the original problem", whatever the original problem might be. But in any case, this approach DOES produce the correct number of hours for the schedule of 609.
