Complex Reporting

(and building a source form to understand how the report results are derived)

Sometimes you need to build a really complex report. I had to do that for one of my Fortune 500 clients. They were trying to automate a 20 hour/mo excel process. The final result was a push of a button solution.

 

But now came the really tough part. Did they believe the results of the report? Well, I can almost guarantee you that each month there are a few numbers in the report that they don’t agree with. I can further guarantee you that the report is correct and that their numbers are wrong. What’s the cause of the discrepancy? Could be a billion different reasons and indeed that’s the case. It’s almost always something to do with the data they are receiving from one of their vendors to create the report.

So what do you do when you’ve built a super complex report and constantly have to justify your numbers? Well, here’s what I did.

  1. Break your reporting process into a series of staging table.
  2. The create a form for checking each staging table for accuracy.
  3. For the queries that build and massage/scrub the queries, add buttons to the form that opens these queries in design view
  4. Label your process as necessary to provide guidance on what the heck is going on
  5. Flow chart the entire process to make it easy to understand from a bird’s eye view

Here’s an example of the above.

Here is how the report is built at the touch of a button:

The client opens a report form, supplies a desired month and year, and chooses to preview the report.

At this screen, before running the report, they can make changes to the report data or review elements of the report prior to running (the 3 buttons on the left, one of which opens the above form)

Here’s the code that runs in the background:

MsgBox “Reminder: Enter SMWE Information first”

‘GoTo skip0

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘Medical

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CurrentDb.Execute “delete * from SSN_Detail”

CurrentDb.Execute “delete * from trpt_CIGNA_Med_Headcount”

CurrentDb.Execute “delete * from trpt_CIGNA_Med_Headcount_Retiree_Only”

CurrentDb.Execute “delete * from trpt_CIGNA_Med_Headcount_Non_Retiree_Only”

 

CurrentDb.Execute “qryMk_SSN_Detail”

‘update and remove duplicates from th eSSN_Detail table – this accounts for changes in status and tier

CurrentDb.Execute “DeleteDupStatusQueryfromSSN_DETAIL”

CurrentDb.Execute “DeleteDupTierChgQueryfromSSN_DETAIL”

 

CurrentDb.Execute “qapp_CIGNA_Med_Headcount”

CurrentDb.Execute “UPDATE trpt_CIGNA_Med_Headcount INNER JOIN tlkp_CIGNA_Plans ON (trpt_CIGNA_Med_Headcount.stat_c_nm_s = tlkp_CIGNA_Plans.stat_c_nm_s) AND (trpt_CIGNA_Med_Headcount.comm_x = tlkp_CIGNA_Plans.comm_x) ” & _

“SET trpt_CIGNA_Med_Headcount.ROPlan = ‘RO Only’ ” & _

“WHERE RetireeOnly=True and InsuranceCompany = ‘CIGNA'”

‘desire to aggregate the USTs execept for UST – IWSE

CurrentDb.Execute “UPDATE trpt_CIGNA_Med_Headcount SET trpt_CIGNA_Med_Headcount.Company = ‘UST’ ” & _

“WHERE Company Like ‘*ust*’ AND Company<>’UST – IWSE'”

 

CurrentDb.Execute “qapp_CIGNA_Med_Headcount_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Med_Headcount_Non_Retiree_Only”

skip0:

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘Dental

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CurrentDb.Execute “delete * from tblHC_Dental”

 

CurrentDb.Execute “qryMK_HC_Dental”

‘update and remove duplicates from the HC_Dental table

‘this accounts for changes in status and tier

CurrentDb.Execute “qryHC_Dental_Del_Status”

CurrentDb.Execute “qryHC_Dental_Del_Tier”

 

‘aggregate UST companies

CurrentDb.Execute “delete * from tblHC_Dental_Agg_UST”

CurrentDb.Execute “INSERT INTO tblHC_Dental_Agg_UST ( Company ) ” & _

“SELECT tblHC_Dental.*, tblOrgCodes.Company ” & _

“FROM tblHC_Dental INNER JOIN tblOrgCodes ON tblHC_Dental.jborgcd_c = tblOrgCodes.Org_Code”

CurrentDb.Execute “UPDATE tblHC_Dental_Agg_UST SET tblHC_Dental_Agg_UST.Company = ‘UST’ ” & _

“WHERE Company Like ‘*ust*’ AND Company<>’UST – IWSE'”

 

‘bring in ROPlan info and create staging table

CurrentDb.Execute “delete * from trpt_CIGNA_Dental_Retiree_Only”

CurrentDb.Execute “delete * from trpt_CIGNA_Dental_Non_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Dental_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Dental_Non_Retiree_Only”

 

‘GoTo skip1:

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘Vision

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

CurrentDb.Execute “delete * from tblHC_Vision”

CurrentDb.Execute “qryMK_HC_Vision”

‘update and remove duplicates from the HC_Vision table

‘this accounts for changes in status and tier

CurrentDb.Execute “qryHC_Vision_Del_Status”

CurrentDb.Execute “qryHC_Vision_Del_Tier”

 

‘aggregate UST companies

CurrentDb.Execute “delete * from tblHC_Vision_Agg_UST”

CurrentDb.Execute “INSERT INTO tblHC_Vision_Agg_UST ( Company ) ” & _

“SELECT tblHC_Vision.*, tblOrgCodes.Company ” & _

“FROM tblHC_Vision INNER JOIN tblOrgCodes ON tblHC_Vision.jborgcd_c = tblOrgCodes.Org_Code”

CurrentDb.Execute “UPDATE tblHC_Vision_Agg_UST SET tblHC_Vision_Agg_UST.Company = ‘UST’ ” & _

“WHERE Company Like ‘*ust*’ AND Company<>’UST – IWSE'”

 

‘bring in ROPlan info and create staging table

CurrentDb.Execute “delete * from trpt_CIGNA_Vision_Retiree_Only”

CurrentDb.Execute “delete * from trpt_CIGNA_Vision_Non_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Vision_Retiree_Only”

CurrentDb.Execute “qapp_CIGNA_Vision_Non_Retiree_Only”

 

 

 

 

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

‘FSA

‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CurrentDb.Execute “delete * from tblHC_FSA_Agg_UST”

CurrentDb.Execute “qapp_CIGNA_FSA”

 

CurrentDb.Execute “UPDATE tblHC_FSA_Agg_UST SET Company = ‘UST’ ” & _

“WHERE Company Like ‘*UST*’ AND Company<>’UST – IWSE'”

 

 

CurrentDb.Execute “delete * from trpt_CIGNA_FSA_agg”

CurrentDb.Execute “qapp_CIGNA_FSA_agg”

 

CurrentDb.Execute “delete * from trpt_CIGNA_FSA_agg_Crosstab”

CurrentDb.Execute “qapp_CIGNA_FSA_agg_Crosstab_app”

 

skip1:

‘build the report

 

CurrentDb.Execute “delete * from trpt_CIGNA”

 

‘append the line items from the Benefit Options and Rates table

CurrentDb.Execute “INSERT INTO trpt_CIGNA ” & _

“SELECT tlkp_CIGNA_Benefit_Options_And_Rates.* ” & _

“FROM tlkp_CIGNA_Benefit_Options_And_Rates ” & _

“WHERE tlkp_CIGNA_Benefit_Options_And_Rates.Order Is Not Null”

 

‘aggregate the UST info

CurrentDb.Execute “UPDATE trpt_CIGNA SET Company = ‘UST’ ” & _

“WHERE (((trpt_CIGNA.Company) Like ‘*UST*’) AND ((trpt_CIGNA.Company)<>’UST – IWSE’))”

 

 

 

‘now update all of the headcount values and perform calculations

‘Medical

CurrentDb.Execute “qupd_CIGNA_Med_Non_Retiree”

CurrentDb.Execute “qupd_CIGNA_Dental_Non_Retiree”

CurrentDb.Execute “qupd_CIGNA_Vision_Non_Retiree”

CurrentDb.Execute “qupd_CIGNA_FSA_Non_Retiree”

 

CurrentDb.Execute “qupd_CIGNA_Med_Retiree”

CurrentDb.Execute “qupd_CIGNA_Dental_Retiree”

CurrentDb.Execute “qupd_CIGNA_Vision_Retiree”

 

‘append SMWE data

CurrentDb.Execute “INSERT INTO trpt_CIGNA SELECT trpt_CIGNA_SMWE.* FROM trpt_CIGNA_SMWE”

 

 

DoCmd.OpenReport Me.OpenArgs, acViewPreview

 

Here’s a page from the actual report:

 

This entry was posted in Access 2007 problems/solutions. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *