need to make new field name in crosstab generic

[bkel]

I have 2 crosstab queries

Here is sql for 1
TRANSFORM Sum(qryExpenseHandleCategory.ForecastExpense) AS SumOfForecastExpense
SELECT qryExpenseHandleCategory.Mega, qryExpenseHandleCategory.Category, qryExpenseHandleCategory.ReportCategory2
FROM qryExpenseHandleCategory
GROUP BY qryExpenseHandleCategory.Mega, qryExpenseHandleCategory.Category, qryExpenseHandleCategory.ReportCategory2
PIVOT qryExpenseHandleCategory.CalendarYear;

Here is the other crosstab

TRANSFORM Sum(qryExpenseHandleCategory.PlanExpense) AS SumOfPlanExpense
SELECT qryExpenseHandleCategory.Mega, qryExpenseHandleCategory.Category, qryExpenseHandleCategory.ReportCategory2
FROM qryExpenseHandleCategory
GROUP BY qryExpenseHandleCategory.Mega, qryExpenseHandleCategory.Category, qryExpenseHandleCategory.ReportCategory2
PIVOT qryExpenseHandleCategory.CalendarYear;

then I combine them using a select query
Here is that sql
SELECT ExpenseCrossTabPlanExpense.Mega, ExpenseCrossTabPlanExpense.Category, ExpenseCrossTabPlanExpense.ReportCategory2, ExpenseCrossTabForecastExpense.[2010], ExpenseCrossTabPlanExpense.[2011], ExpenseCrossTabForecastExpense.[2011]
FROM ExpenseCrossTabPlanExpense INNER JOIN ExpenseCrossTabForecastExpense ON (ExpenseCrossTabPlanExpense.Mega = ExpenseCrossTabForecastExpense.Mega) AND (ExpenseCrossTabPlanExpense.Category = ExpenseCrossTabForecastExpense.Category) AND (ExpenseCrossTabPlanExpense.ReportCategory2 = ExpenseCrossTabForecastExpense.ReportCategory2);

I made a report based on this combined SQL - but the Controls in the body of the report are set to Sumof2010 ect. How can I make this not be the case

I do not want to have to modify the controls withing the header and footer section of the report for each new 2 years i want to look at????

 

[dhookom]

There are 2 FAQs that should provide a near complete solution:
faq701-4524
faq703-5466

Duane
Hook'D on Access
MS Access MVP

 

[bkel]

Assuming on my problem where Month is below I would put
Year([CalendarYear])
your Column Heading expression is
Expr1:[FldName] & Month([OrderDate])

Need PlanExpense for prev year(2010 for now) and Plan Expense and Forecast Expense for 2011( next year dif)

your Value expression is
DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
Not sure what I put for my Value expression - see my original post??
thanks a ton - have been trying to get this for a month!!
bkel

 

[bkel]

sorry actually need Forecastexpense 2010(wrong-prev.post) and planexpense & forecastexpense 2011
sorry,
bkel

 

[dhookom]

Did you create a two record table for the multivalue crosstab? This would have records with Forecast and Plan. Then value your crosstab would be something like:

DaVal:IIf([FldName]="Forecast",[ForecastExpense],[PlanExpense])

Your Column heading values would be something like

[FldName] & Year(Date())-CalendarYear

This is all a guess since I can't see your data types, tables, etc.


Duane
Hook'D on Access
MS Access MVP

 

[bkel]

Did you create a two record table for the multivalue crosstab? This would have records with Forecast and Plan.
YOUR ASSUMTPION ABOVE IS CORRECT. the crosstab looks good.

Here is My Sql that seems good for the Crosstab query
TRANSFORM Sum(IIf([FldName]="Forecast",[ForecastExpense],[PlanExpense])) AS DaVal
SELECT qryExpenseHandleCategory.Mega, qryExpenseHandleCategory.Category, qryExpenseHandleCategory.ReportCategory2
FROM qryExpenseHandleCategory, tblXtabColumns
GROUP BY qryExpenseHandleCategory.Mega, qryExpenseHandleCategory.Category, qryExpenseHandleCategory.ReportCategory2
PIVOT [FldName] & Year(Date())-[CalendarYear];

Now to the report
I will make a Form - that asks for Start year(CalendarYear) - and End Year(CalendarYear)
And a combo box to select What Mega they want a cboMega- would Filter on that

My goal ref. my first post - is to have column headings :

ForecastExpense 2010 PlanExpense 2010 ForecastExpense 2011

My groups down are by Category - then by ReportCategory2:
need to have a sum so are in the Group Footer sections for each of the category and reportcategory2 groupings

Not sure how to modify the Link for the Report to handle this
Confused on the year and the right syntax with my crosstab- see my sql above.

thanks sooo much,
Bkel

 

[bkel]

Not sure if this matters but usually only 2 calendar years look at - Present year and previous year.

thanks again!!!!!!! bkel

 

[bkel]

Sorry one more thing I just notice with the crosstab query the way it is above - when I put in 2009 and 2010 I get ForecastExpense1 and ForecastExpense2 and same for planexpense
When I do 2010 and 2011 I get ForecastExpense0 and ForecastExpense1 and same for planexpense for my columnHeading of the results of the crosstab- not clear on how the sum(ForecastExpense1) can be generic in the group footer and handle either year cominations -assuming the second link that you will help out with above will handle this.

Thinking that if the column Heading can just be a Generic Name.
I can use the column Heading Label to just grab what are the years selected on the form???

thansk,
bkel

 

[bkel]

Alright - noticed something else when I added the tblXtabColumns table to the crosstab query my totals of the groups of Mega by category by reportcategory2 are less before I added that table??? seems like it does not account for all of the data??????

Please keep helping - and Thanks a ton!!
bkel

 

[dhookom]

The monthly crosstab FAQ suggests how to use a control on a form to set the ending date. It also has details on how to get the column labels to appear correctly in the report.

You will need to set the data types of your parameters in the crosstab query.

Duane
Hook'D on Access
MS Access MVP