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
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
ASKER
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.
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.
ASKER
Should I have made parent child tables with the child structured something like?
AllowanceCode
AllowanceUnits
AllowanceCode
AllowanceUnits
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Dale, that makes sense. I will change the structure as you suggest.
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
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
ASKER
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?
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?
Rob,
Please start a new thread.
Please start a new thread.
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
Sent from my iPad
ASKER
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.
https://www.experts-exchange.com/questions/28706459/Which-is-best-Multiple-fields-or-multiple-records.html
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.
https://www.experts-exchange.com/questions/28706459/Which-is-best-Multiple-fields-or-multiple-records.html
Not with the structure that you have.