D
darcybaston
Guest
I need to take information from a posting table that looks like this (with sample data):
R is for regular pay, O is for overtime in the "type" fields.
payperiod:1
week:1
entrydate:02/21/02
employeename
arcy B
employeenumber:007
contracthours:8
contracttype:R
contractrate:$19.95
officehours:2
officetype:O
officerate:$29.93
So field names are at left, sample record data at right. These are timesheet entries that are eventually posted from a temporary table to a history table. No problems there.
Now, I need to make a report that sums all R for week1, all R for week2, all O for week1, all O for week 2, multiply the hours by the proper rates and show the dollar value (for week1, week2 and both weeks added together). I also need to show total indisciminant hours per week (R and O combined) This report needs to only grab history entries based on beginning/end dates of a pay period as criteria.
I can do the date querying no problem, it's getting the totals to generate so that I can have each calculation or field output in a column (left to right) and not up and down like report groups do. So it would look like:
PP-Name-Number-Week1-Week2-R1-O1-etc.
I tried this in one single query, but with all the different permutations of R, week1, O, week2 duplicates are showing up and the query sigma sum stuff just isn't working. (A single value will be summed as many times as there are records instead of just being included once.) I've also tried to do this kind of report using grouping, but again, it's vertical and not horizontal like I need it.
I've tried a crosstable query thing, but my date comparison criteria triggers a Jet Database Engine 4 error because the dates come from a form holding data in the background.
Don't have the code in front of me but the date thing looks like:
>=Forms![frm_posting].[startdate] and <= Forms![frm_posting].[enddate]
Works fine in regular queries, but the cross table not only doesn't generate the 20+ columns I need to make, but it flags that comparison thing.
I'm just lost. Can't get this darn payroll report to work using a columns visual approach.
It was working with option '2' in the relationship properties when I tried splitting each different R week1, R week2 combination query, but given enough records and permutations data duplicates again.
So...if anyone has done a payroll report before in columnar format, resue me?
regards,
darcybaston@mac.com
---light bulb---
It just occured to me that I should maybe just dump the table into a columnar report and hide the details of each table record, and just show the employee ID info followed by a summing group section for the other weekly totals or something. Hmmm....
----
!FOUND A SOLUTION!
My problem was actually more complex then I mentioned, but here is one fix with complexities intact.
Knowing that there are (3) possible hourly reates (R,O,X), and (2) cost categories (additional work and contract) and the there are (2) different weeks in a pay period to take into consideration, I get a permutation calculation of 3x2x2, which gives me 12 possible combinations.
I created one summation query for each one of those permutations (ex: week1_R_ADD, week2_O_CON and so on) and appended the results to a table (using a Macro that ran all 12 append queries to that table) with the 12 fields ready to accept resulting calculations. Of course, while appending, one field will get a number and the rest will be 0s, but this is fine because later I consolidate that table into a report grouping by employee and the sums of all colums just add 0s and don't harm the totals.
Works beautifuly. I now have an Excel like payroll report with varying calculations of week totals, regular and overtime totals, week1_regular_totals and so on. Anything I need.
[This message has been edited by darcybaston (edited 02-23-2002).]
R is for regular pay, O is for overtime in the "type" fields.
payperiod:1
week:1
entrydate:02/21/02
employeename
employeenumber:007
contracthours:8
contracttype:R
contractrate:$19.95
officehours:2
officetype:O
officerate:$29.93
So field names are at left, sample record data at right. These are timesheet entries that are eventually posted from a temporary table to a history table. No problems there.
Now, I need to make a report that sums all R for week1, all R for week2, all O for week1, all O for week 2, multiply the hours by the proper rates and show the dollar value (for week1, week2 and both weeks added together). I also need to show total indisciminant hours per week (R and O combined) This report needs to only grab history entries based on beginning/end dates of a pay period as criteria.
I can do the date querying no problem, it's getting the totals to generate so that I can have each calculation or field output in a column (left to right) and not up and down like report groups do. So it would look like:
PP-Name-Number-Week1-Week2-R1-O1-etc.
I tried this in one single query, but with all the different permutations of R, week1, O, week2 duplicates are showing up and the query sigma sum stuff just isn't working. (A single value will be summed as many times as there are records instead of just being included once.) I've also tried to do this kind of report using grouping, but again, it's vertical and not horizontal like I need it.
I've tried a crosstable query thing, but my date comparison criteria triggers a Jet Database Engine 4 error because the dates come from a form holding data in the background.
Don't have the code in front of me but the date thing looks like:
>=Forms![frm_posting].[startdate] and <= Forms![frm_posting].[enddate]
Works fine in regular queries, but the cross table not only doesn't generate the 20+ columns I need to make, but it flags that comparison thing.
I'm just lost. Can't get this darn payroll report to work using a columns visual approach.
It was working with option '2' in the relationship properties when I tried splitting each different R week1, R week2 combination query, but given enough records and permutations data duplicates again.
So...if anyone has done a payroll report before in columnar format, resue me?
regards,
darcybaston@mac.com
---light bulb---
It just occured to me that I should maybe just dump the table into a columnar report and hide the details of each table record, and just show the employee ID info followed by a summing group section for the other weekly totals or something. Hmmm....
----
!FOUND A SOLUTION!
My problem was actually more complex then I mentioned, but here is one fix with complexities intact.
Knowing that there are (3) possible hourly reates (R,O,X), and (2) cost categories (additional work and contract) and the there are (2) different weeks in a pay period to take into consideration, I get a permutation calculation of 3x2x2, which gives me 12 possible combinations.
I created one summation query for each one of those permutations (ex: week1_R_ADD, week2_O_CON and so on) and appended the results to a table (using a Macro that ran all 12 append queries to that table) with the 12 fields ready to accept resulting calculations. Of course, while appending, one field will get a number and the rest will be 0s, but this is fine because later I consolidate that table into a report grouping by employee and the sums of all colums just add 0s and don't harm the totals.
Works beautifuly. I now have an Excel like payroll report with varying calculations of week totals, regular and overtime totals, week1_regular_totals and so on. Anything I need.
[This message has been edited by darcybaston (edited 02-23-2002).]
