Which is best Multiple fields or multiple records
This is a follow on from the following question: https://www.experts-exchange.com/questions/28706118/Cross-Tab-on-flat-record.html?anchorAnswerId=40932777#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
OJTAllow
OwnCarAllow
TravelAllow
BaseHours
THalfHours
DTHours
ALHours
LoadingHours
SickLeave
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
OJTAllow
OwnCarAllow
TravelAllow
BaseHours
THalfHours
DTHours
ALHours
LoadingHours
SickLeave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"add a flag to the record of being not wanted"
Is that more efficient than deleting? I thought deleting a record basically did the same thing and the record is only removed when the database is compacted
Is that more efficient than deleting? I thought deleting a record basically did the same thing and the record is only removed when the database is compacted
---No Points wanted
Agree with hnasr...
An old saying:
Agree with hnasr...
An old saying:
Records are cheap, fields are expensive
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to everyone for your comments. Converting to normalized file is clearly the right solution. Thanks for your patience in explaining it all. I still don't know how to easily recalculate the values every time I need them so I don't have to save them but will work on that.
ASKER
Thanks for your added comments.
1. I take understand your confusion. The main reason I was trying to calculate and save was to make it easier later to extract data without having to recalculate.
I am still uncertain about how to do that effectively with your recommended approach (sorry if I'm not getting this just yet). As I see it I can't create a query that can calculate these values easily. I need to create a recordset that will loop through the table recreating the outputs and saving them in a temp file. Otherwise I need to create a function that is called by a query that creates a recordset and loops through the relevant records for every line. I read your article on how you can create a STATIC recordset that would make it quicker, but it is still a lot of processing every time I want to review the data
BTW if it's not clear why I have to loop through, First of all I need to find out if there are any carry forward shortfall in Base Hours from the previous day(s) worked so as to know what the BaseHours limit is for the segment I am working with. I then need to keep track of the cumulative hours worked so far that day as I process each shift segment in order to calculate the Base, THalf and DTime hours components of that shift segment. I also have to know how many segments there are in that day so I can work out the proportion of the allowances attributable to that shift segment.
2. Employees are entitled to two fixed $ allowances per day. One of them is a "Turn Up For Work" Allowance - if they turn up they get it (gotta love that one). They also get an allowance if they use their own transport to get the the job or a travel time allowance if they get picked up from home. This cost needs to be apportioned against all the jobs they do in a day. I don't store dollars, these are stored as units and the total for each day must be either 0 or 1.
3. ALHours is Annual Leave hours. Some employees also get a 17.5% loading on their Annual Leave pay so here I record the number of hours that attract this loading
I haven't mentioned it earlier but much of this information needs to be subsequently be turned into a csv file that is then exported into the accounting software. Getting MS Access to do the calculations and create the csv saves a lot of headaches and makes report extraction easier.