Work Comp Reports Program using Access 2010
All data is exported from Quickbooks to excel then imported to Access using Transfer datasheet
One Form used to enter date range and to open reports in preview (frmWcReport)
One Query is the basis for all Reports, just grouped as needed (qryWcPayroll)
Payroll Report – Detailed report used for the annual audit showing breakdown of Gross Wages and then Wages subject to Premium (Wc Wages) – Works fine
Grouped by WcCode and then by Name (one name can appear under several codes)
**I included this report because I wasn’t sure if the next report should be a subreport and this the main
Premium Report – Summary report used to calculate the cost per department and replicate the billing form from the insurance company. I use this report to compare to the completed annual audit and to make my monthly Journal entries
Grouped by Departments
Page 1 Class Footer
Each Dept is summarized and listed separately with:
Dept, WcCode, WcWages, WcCodeRate, WcGrossClassPrem (WcWages x WcCodeRate), QBprem
(QBprem is premium imported from QB)
WcWages, WcGrossClassPrem are totaled in report footer
Page2 Report Footer
This is where I replicate the insurance form in order to calculate the Net Premium. There are about a dozen different fees and discounts that are added or subtracted from the GrossPrem. Using the Total WcWages and the Total WcGrossClassPrem in the report footer as my basis, I pull the rates for these insurance fees from my query and place them on the report (bound) in addition to 25 unbound textboxes used to hold the results of all of the calculations. This was a tedious process but it works and all I have to do is compare my report to the audit.
However, I need to do the same calculations per department and place just the result in the class footer so that it has:
Dept, WcCode, WcWages, WcCodeRate, WcGrossClassPrem, WcNetClassPrem, QBprem, JourEntry
Where the JourEntry (WcNetClassPrem – Qbprem) gives me my monthly journal entries per department.
Do I need a function? How would I pull the fee rates into it? Should I put all of the calculations in a sub procedure? I should only have to create these calculations once somewhere right, since I’ll probably have to use them at least twice per session? I still have 2 more reports to attach but I’m stuck here.
Been using Access for years and a little vba, still a novice. Please be as specific as possible. Thanks
All data is exported from Quickbooks to excel then imported to Access using Transfer datasheet
One Form used to enter date range and to open reports in preview (frmWcReport)
One Query is the basis for all Reports, just grouped as needed (qryWcPayroll)
Payroll Report – Detailed report used for the annual audit showing breakdown of Gross Wages and then Wages subject to Premium (Wc Wages) – Works fine
Grouped by WcCode and then by Name (one name can appear under several codes)
**I included this report because I wasn’t sure if the next report should be a subreport and this the main
Premium Report – Summary report used to calculate the cost per department and replicate the billing form from the insurance company. I use this report to compare to the completed annual audit and to make my monthly Journal entries
Grouped by Departments
Page 1 Class Footer
Each Dept is summarized and listed separately with:
Dept, WcCode, WcWages, WcCodeRate, WcGrossClassPrem (WcWages x WcCodeRate), QBprem
(QBprem is premium imported from QB)
WcWages, WcGrossClassPrem are totaled in report footer
Page2 Report Footer
This is where I replicate the insurance form in order to calculate the Net Premium. There are about a dozen different fees and discounts that are added or subtracted from the GrossPrem. Using the Total WcWages and the Total WcGrossClassPrem in the report footer as my basis, I pull the rates for these insurance fees from my query and place them on the report (bound) in addition to 25 unbound textboxes used to hold the results of all of the calculations. This was a tedious process but it works and all I have to do is compare my report to the audit.
However, I need to do the same calculations per department and place just the result in the class footer so that it has:
Dept, WcCode, WcWages, WcCodeRate, WcGrossClassPrem, WcNetClassPrem, QBprem, JourEntry
Where the JourEntry (WcNetClassPrem – Qbprem) gives me my monthly journal entries per department.
Do I need a function? How would I pull the fee rates into it? Should I put all of the calculations in a sub procedure? I should only have to create these calculations once somewhere right, since I’ll probably have to use them at least twice per session? I still have 2 more reports to attach but I’m stuck here.
Been using Access for years and a little vba, still a novice. Please be as specific as possible. Thanks
