2 Crosstab Queries Used For Report Data Source 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 and groups [ctr] of line items. Each Crosstab works fine consolidating the Line Item Groups 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 ActualSELECT BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName, Sum(BranchMasterTbl.[SumOfCurrent Period $]) AS YTDActualFROM Calendar INNER JOIN BranchMasterTbl ON (Calendar.Year = BranchMasterTbl.Year) AND (Calendar.Period = BranchMasterTbl.Period)GROUP BY BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchNamePIVOT Calendar.Period;

------------------------------------------------------------

CrosstabBudget:

TRANSFORM Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS BudgetSELECT BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchName, Sum(BranchMasterTbl.[SumOfCurrent Period Budget $]) AS YTDBudgetFROM (Calendar INNER JOIN BranchMasterTbl ON (Calendar.Year = BranchMasterTbl.Year) AND (Calendar.Period = BranchMasterTbl.Period)) INNER JOIN ChartOfAccounts ON BranchMasterTbl.Account = ChartOfAccounts.AccountGROUP BY BranchMasterTbl.ScorecardLine, BranchMasterTbl.Ctr, BranchMasterTbl.BranchNamePIVOT Calendar.Period;

 

[dhookom]

There is at least one reply/solution at thread701-1666666. It's best if you post a question in only one forum.

Duane
Hook'D on Access
MS Access MVP