Report Design Using 2 Crosstab Queries 1

[JStrand]

I have been struggling with a good design for combining 2 crosstab qrys CrosstabActual and CrosstabBudget to be used in a single report. The report layout is to have 12 months in the column heading,Income Statement Accounts as row headings and Amounts as values. I need the Actual values to display in one row above the budget values for many income statement line items. Each Crosstab works fine with exception of placeholders for months where no actual data yet exists. Any advice on combining these two croosstabs to be used as one object for a report reference would be helpful. Thank you.

CrosstabActual:

TRANSFORM Sum(BranchMasterTbl.[SumOfCurrent Period $]) AS Actual
SELECT BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName, Sum(BranchMasterTbl.[SumOfCurrent Period $]) AS YTDActual
FROM Calendar INNER JOIN BranchMasterTbl ON (Calendar.Year = BranchMasterTbl.Year) AND (Calendar.Period = BranchMasterTbl.Period)
GROUP BY BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName
PIVOT Calendar.Period;

------------------------------------------------------------
CrosstabBudget:

TRANSFORM Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS Budget
SELECT BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName, Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS YTDBudget
FROM (Calendar INNER JOIN BranchMasterTbl ON (Calendar.Year = BranchMasterTbl.Year) AND (Calendar.Period = BranchMasterTbl.Period)) INNER JOIN ChartOfAccounts ON BranchMasterTbl.Account = ChartOfAccounts.Account
GROUP BY BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName
PIVOT Calendar.Period;

 

[dhookom]

Each Crosstab works fine with exception of placeholders for months where no actual data yet exists

You can use the Column Headings property to enter all periods. I'm not sure what you are storing in periods but assuming numbers 1-12. I would also add a column to each crosstab to identify whether the results are Actual or Budget. You could then combine them in a UNION query.

TRANSFORM Sum(BranchMasterTbl.[SumOfCurrent Period $]) AS Actual
SELECT "Actual" as ActBud, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName, Sum(BranchMasterTbl.[SumOfCurrent Period $]) AS YTDActual
FROM Calendar INNER JOIN BranchMasterTbl ON (Calendar.Year = BranchMasterTbl.Year) AND (Calendar.Period = BranchMasterTbl.Period)
GROUP BY BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName
PIVOT Calendar.Period In (1,2,3,4,5,6,7,8,9,10,11,12);

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

When I combine these crosstabs in a union query all looks great. When using the union query as the datasource in my report I'm getting 52 pages of same result. 52 is the number of records in one of the crosstab queries. Can you see something in the crosstab(s) which would cause this effect?

Since it takes several fields to define a unique record is there a method to add a primary key (record counter)to each record in the crosstab query result?


Actual Crosstab

TRANSFORM Sum(BranchMasterTbl.[SumOfCurrent Period $]) AS Amount
SELECT BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, "Actual" AS ActBud, [ReportGroup3] & "Actual" AS ReportGroupBook, Sum(BranchMasterTbl.[SumOfCurrent Period $]) AS YTDAmount
FROM BranchMasterTbl
GROUP BY BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, "Actual", [ReportGroup3] & "Actual"
ORDER BY BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr
PIVOT BranchMasterTbl.Period In (1,2,3,4,5,6,7,8,9,10,11,12);

Budget Crosstab

TRANSFORM Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS Amount
SELECT BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, "Budget" AS ActBud, [ReportGroup3] & "Budget" AS ReportGroupBook, Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS YTDAmount
FROM BranchMasterTbl
GROUP BY BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, "Budget", [ReportGroup3] & "Budget"
ORDER BY BranchMasterTbl.Category, BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr
PIVOT BranchMasterTbl.Period In (1,2,3,4,5,6,7,8,9,10,11,12);


Union Qry

Select *

From ScorecardBranchQry_Crosstab_Actual

UNION Select *

From ScorecardBranchQry_Crosstab_Budget;

 

[dhookom]

The number of pages has no meaning to us. Tell us how many records are displaying in each query and how many in the union query. Also tell us how many you expect to see.

Are ther any sub reports in your report?

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thank you for your response Duane.

52 is the number of records displaying in each of the crosstab query results. 104 records is the number in the union query. This is expected. There are no subreports in the report. Page 1 of the report yields all expected results, and then 51 additional pages of the header and detail appear identical to page 1.

Now when attempting to add additional line items in the detail section I receive the error "Too many fields defined". This may be caused by whatever is driving the additional 51 pages of data to be calculated... This problem may be solved by correcting the first problem. Thanks for your help in thinking about this.

 

[dhookom]

Please provide the exact SQL of the record source of your report. Your statement "adding additional line items" suggest you aren't stopping at just the union query.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Hi Duane, the SQL I provided for the Union Qry (above) and underlying crosstabs queries (above) is the only record source of the report. The additional line items I referred to are control sources within the report querying data from the union Qry. What I'm callin a typical line item in the detail section of my report consists of an Unbound Label such as Gross Revenue - Landscape, and 26 control source calculations in crosstab format. One for each month of the year plus YTD for Both Actual and Budget.

I've attached a word document with a printscreen of the report design view which may do a better job of describing this. I added the SQL for a typical control source calculation for one month beneath the screenshot as well as the orignal SQL for the Union Qry and Crosstabs. Please let me know if this helps. Thank you again for your help.

 

[dhookom]

Your issue is that your report contains no detail information so nothing should be in the detail section. If all you want is report totals, just cut and paste all of the controls from the detail section to the Report Footer section (not the page footer) and make the detail section either 0 height or invisible.


Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thank you Duane. That works and my report is now 1 page. However, now when attempting to add additional line items in the detail section I receive the error "Too many fields defined". I still have about 50% more line items as I've previously described to add to the report. I thought by eliminating the extra pages this error would go away, but no such luck.. Any suggestions on this would be appreciated.

 

[dhookom]

I think you could have designed your tables and queries so that each pair of Budget and Actual had their own sums rather than creating so many text boxes with complex expressions.

I think I suggested before that data belongs in your tables and not your code/expressions. Each of the label captions on the left should be data values pulled from tables and available in your queries.

If you don't want to redo this, you might consider compacting your database and/or using subreports and/or code.

Duane
Hook'D on Access
MS Access MVP

 

[JStrand]

Thank you Duane! The sub report solution is working. I can use this as a short term solution while thinking through the redesign of my queries which will summarize the data before calling it too the report. Thanks again for your help.