Which is best Multiple fields or multiple records

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:

You know where I stand on this, so I'll generally stand back and look for other experts to comment here.  But I do have a couple of questions.

1.  This layout you are describing above is for casual viewing of a single employees hours for the week, not by work segment, but total hours.  Why do you feel you need to store this data permanently?  If you have an HoursWorked table, which gives you the ability to store the EmpID, Date, Project, StartTime, EndTime, you can recompute this data for a particular employee whenever you want.  I might have a temp table to populate which would give you the data you need for this cross tab, but do not see any reason to save that data as it can be recomputed quickly whenever you want to see it based on the Hours Worked table.

2.  I'm still not sure I understand the concept of "Allowances" and how those contribute to hours worked?

3.  What are ALHours and LoadingHours?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
Hi Dale,

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.
I prefer records to multiple fields.

Having records, one can easily produce a query with multiple fields.
It is simpler to maintain.
It has the advantages of a normalized database.

You don't need to delete data, but add a flag to the record of being not wanted, and filter data to exclude unwanted records.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob4077Author Commented:
"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
Jeffrey CoachmanMIS LiasonCommented:
---No Points wanted
Agree with hnasr...
An old saying:
Records are cheap, fields are expensive
Jeffrey CoachmanMIS LiasonCommented:
It is not as simple as that.
90% of the time when a record is deleted, ..it will be unrecoverable.
A lot of things happen behind the scenes (internally) in an Access db.
Setting a flag is the most effective way of "effectively" deleting (or hiding) a record, ...but still being able to recover it.
Compacting a database, reclaims the empty space in the database. It is similar, to some extent, to defragmentation of a disk.

When you add a control to a form it uses disk space in the database. When you delete the control, it is gone and leaves its space as empty, and the size of the database remains the same. Compacting the database, rearranges the objects, and reuses the empty space, and the database size may be, considerably, reduced.

A deleted record is almost forgotten by the database unless you have a management procedure to save the record, somewhere, before delete.
Rob4077Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.