Multiple Value Crosstab Query Report

[LimAccess]

Hi

I'm trying to create a report that compares budgeted values to actual throughput values grouped by customer, state, branch and product type.

I have created 2 crosstab queries: one for the budgeted values and one for the actual.

I've attached a picture of how I'd like the report to look.
Here's the SQL for one of the crosstab querie (they are both the same- just for different data sets):

TRANSFORM Nz(Sum(tbl_Jul2014_Jan2016_Complete_Thruput.ChargeableQuantity))+0 AS SumOfChargeableQuantity
SELECT tbl_Partners.Partner, tbl_Agency_States.State_Name, tbl_Jul2014_Jan2016_Complete_Thruput.AgentCompanyName
FROM tbl_Year INNER JOIN (tbl_Species INNER JOIN (tbl_Partners INNER JOIN (tbl_Months INNER JOIN (tbl_Agency_States INNER JOIN tbl_Jul2014_Jan2016_Complete_Thruput ON tbl_Agency_States.Agency_Thruput_State_ID = tbl_Jul2014_Jan2016_Complete_Thruput.Agency_Thruput_State) ON tbl_Months.Month_ID = tbl_Jul2014_Jan2016_Complete_Thruput.Thruput_Month) ON tbl_Partners.ID = tbl_Jul2014_Jan2016_Complete_Thruput.Thruput_Partner) ON tbl_Species.ID = tbl_Jul2014_Jan2016_Complete_Thruput.Thruput_Species) ON tbl_Year.Year_ID = tbl_Jul2014_Jan2016_Complete_Thruput.Thruput_Year
WHERE (((tbl_Partners.Partner)="Elders") AND ((tbl_Months.Month_Name)="January"))
GROUP BY tbl_Months.Month_Name, tbl_Partners.Partner, tbl_Agency_States.State_Name, tbl_Jul2014_Jan2016_Complete_Thruput.AgentCompanyName
PIVOT tbl_Species.Species;

 

[MarkK]

Hello there and welcome to the forum.

You haven't asked a question and the topics you raise are rather broad. Are you having trouble, for instance, creating a report? Or are you having trouble making it look like your image? Maybe you can narrow down the problem for us so our answers will be more useful to you.

 

[LimAccess]

Good point Markk!

I'm stuck after making the two crosstab queries. How can I create a query that combines the two data sets?

 

[MarkK]

The problem with Crosstab queries is that you don't know at design time how many columns (fields) there will be, since the fields names are drawn from your data. So designing a report to display a crosstab is fraught with hazard. There are VBA work-arounds, but those require more than a beginners level of programming skill.

What would probably benefit you more in the short term is make sure you understand basic principles of "database normalization." Looking that this table name, "tbl_Jul2014_Jan2016_Complete_Thruput", suggests that you may have some design problems. Dates should not appear in the name of something. Dates are data, and should only appear in a field.