Cross Tab on flat record

I have a table that has the following records that is used as a basis for data entry for:
Id   WorkDate   fkWorkerId   Started   Finished fkDetailId   BaseHours   THalfHours   DTimeHours   ALHours   LoadingHours   SickLeave   OTJAllow   OwnCarAllow   TravelAllow  

There can be anywhere between 1 and 15 entries for the same fkWorkerId for the same WorkDate so, to help ensure the accuracy of the data entry I would like to create a subform on the data entry screen that displays summary data (with only the total for each day by field) in the following format. This looks like a job for a CrossTab Query but from what I understand a CrossTab query requires a much more normalized structure with only one field being SUMmed. I can create a UNION query where each component relates to a different field and then use that query as a basis for a cross tab query but that seems like a very cumbersome solution. Is there an easier way?

                              Fri      Sat      Sun      Mon      Tue      Wed Thu
OJTAllow
OwnCarAllow
TravelAllow
BaseHours
THalfHours
DTHours
ALHours
LoadingHours
SickLeave
LVL 1
Rob4077Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 FyeOwner, Developing Solutions LLCCommented:
"Is there an easier way?"
Not with the structure that you have.
Rob4077Author Commented:
Thanks for the confirmation. Not what I wanted to hear but it's what I needed to know.

Just for my benefit, what would be the recommended table structure given I need to calculate the various allowances and hours breakdown based on the start and end time - it made sense to me to keep all related fields on the same record. The summary is just a double check to make sure it's entered correctly.
Rob4077Author Commented:
Should I have made parent child tables with the child structured something like?
AllowanceCode
AllowanceUnits
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
Not certain what the OJTAllow, OwnCarAllow, TravelAllow fields are, but the others all appear to be related to time recording.  In my time card type applications, I generally have a structure that looks something like:

WorkerID, WorkDay, ProjectID, WorkHours

Where the ProjectID would be particular projects or could reflect SickLeave and Vacation time.  This gives me the ability to quickly assess hours by worker, WorkDay, or Project very simply.

In the Employee record, I store information which would identify whether the employee is salaried (not elegible for Over time) or wage grade (eligible for OT), and if eligible, the number of hours per day or week required before OT kicks in.  If those first three fields are numeric (amounts) rather than Yes/No, then you could identify those as "Projects" as well.

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:
Thanks Dale, that makes sense. I will change the structure as you suggest.
Robert ShermanOwnerCommented:
Bear in mind, too, that you are seeking DOW (Fri, Sat [...] Thurs), but your data I'm guessing is based on WorkDate which is a full dateTime field.   So, you'd need to select just the week of interest before feeding this data into the step you're now looking for.

Once you've gotten beyond that, you can probably do an aggregate query to group by Day taking a sum of the fields you listed.   You'd need to base a grouping on Day(Date) to get your Fri, Sat, Sun... and grouped by worker ID.   From there you could probably link this up as a sub-form to get to your goal of displaying the data for review during data-entry.

Performance wise, this may be a bit tricky.

EDIT: The bottom line being that you might want to follow Dale's suggestions...   :D
Rob4077Author Commented:
Hi Robert, Thanks for your comment. Sorry but I didn't see your comment until after I closed the question. The suggestions you made make sense but confirm somewhat the dilemma I face. The file structure I am using makes it easy to calculate and edit the components but difficult to display a simple summary. Using the normalized structure Dale is suggesting means a lot of adding and deleting records every time a new entry is made.

Dale, if you're still linked in, can I ask another question of you in relation to your recommended approach.

When a new time record is added (a shift segment), or an existing one is edited, I somehow need to create or edit the necessary records in the table you recommended. E.g. in my case a single shift segment may require a Base Hours output, a Time and a half hours output and a double time hours output plus I may need to output an on the job start allowance and a Travel Time Allowance. But since they are only entitled to get one of these allowances per day I need to allocate the appropriate portion to each shift segment (e.g. if there are 3 shift segments in a day I need to allocate 0.34 to the first and 0.33 to the next two), That means every time a shift segment is entered or edited I need to delete all the existing entries for the day, recalculate them and then re-add the records for the entire day. This seems like a lot of processing in order to keep the data normalized as you suggest. Is that still the right approach?
PatHartmanCommented:
Rob,
Please start a new thread.
Dale FyeOwner, Developing Solutions LLCCommented:
I would agree with Pat that this should be discussed as a separate question.  But please put a hyperlink back to this question in the new thread so that any other experts that want to chime in may do so and can see what was discussed in this thread.

Sent from my iPad
Rob4077Author Commented:
Sorry, you're right.

I've raised the following related question and in it I have provided a lot more details to explain why I took the approach I did. Converting it to Dale's recommended structure should be relatively straightforward but I just want to make sure it's the right thing to do.

http://www.experts-exchange.com/questions/28706459/Which-is-best-Multiple-fields-or-multiple-records.html
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.