Access Crosstab Query 2

[NuggetInc]

I'm tring to sum up sales figures by month (there are several entries for each month) using a crosstab query. That part is working fine - I get the months, and the total sales from all customers for that month.

What I also want to do is calculate last year's figures in the same crosstab query. Or calculate last years figures in a different query, then combine the two in a third query.

I'm having trouble doing either. Any solutions?

If this helps, below is the sql of the current query (that works, but only for the current year):

PARAMETERS [Forms]![SalesBySlsmDialog]![BeginYear] Long, [Forms]![SalesBySlsmDialog]![Month] Long, [Forms]![SalesBySlsmDialog]![Salesman] Long, [Forms]![SalesBySlsmDialog]![MfgCombo] Long;
TRANSFORM Sum([All-Mfgs_All-Reps Query].Amount) AS [The Value]
SELECT [All-Mfgs_All-Reps Query].Month, [All-Mfgs_All-Reps Query].MonthID
FROM [All-Mfgs_All-Reps Query]
WHERE ((([All-Mfgs_All-Reps Query].MonthID)<=[Forms]![SalesBySlsmDialog]![Month]) AND (([All-Mfgs_All-Reps Query].Year)=[Forms]![SalesBySlsmDialog]![BeginYear]) AND (([All-Mfgs_All-Reps Query].ImployeeID)=[Forms]![SalesBySlsmDialog]![Salesman]) AND (([All-Mfgs_All-Reps Query].DealerID)=[Forms]![SalesBySlsmDialog]![MfgCombo]))
GROUP BY [All-Mfgs_All-Reps Query].Month, [All-Mfgs_All-Reps Query].MonthID
ORDER BY [All-Mfgs_All-Reps Query].MonthID
PIVOT [All-Mfgs_All-Reps Query].EmployeeLastName;

 

[JonFer]

Include the Year field in the select list and modify the WHERE as follows:

WHERE ((([All-Mfgs_All-Reps Query].MonthID)<=[Forms]![SalesBySlsmDialog]![Month]) AND (([All-Mfgs_All-Reps Query].Year) Between [Forms]![SalesBySlsmDialog]![BeginYear]-1 And [Forms]![SalesBySlsmDialog]![BeginYear]) AND ...

John

http://www.interautomateconsulting.com

 

[NuggetInc]

JonFer,

Thanks for the input. But won't that give me the combined results for last year and this year?

What I wanted (and wasn't clear in my initial post, sorry) is two columns, i.e. :

month 2004 2003
jan $100 $50
feb $150 $100
mar $500 $350
... ... ...

I wanted to get both columns (2003 results, 2004 results), not the combined results. I will be performing calculations on these two columns later.

Thanks.

 

[dhookom]

In your crosstab query, set your month expression/field as the Row Heading and your year expression/field as the column heading. Set the Column Headings property to:
Column Headings:2004,2003


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NuggetInc]

dhookom,

I did that and I get a blank column for 2003, and #s in the 2004 column.

You may also need to know that the user picks the beginning year, and I want to calculate that year's numbers and then compare them to the previous year's numbers.

The problem I'm having is how to get two sets of numbers from the same crosstab query - One set of numbers for the chosen year, and another set of numbers for the previous year.

 

[NuggetInc]

One more thing:

The below text worked fine for summing the DOMAIN (grand total of all months), but I want to break out the months seperately (jan, feb, mar,...):

Prior Year: IIf(DSum("Amount","All-Mfgs_All-Reps Query","Year=" & [Forms]![SalesBySlsmDialog]![BeginYear]-1 & " And MonthID<=" & [Forms]![SalesBySlsmDialog]![Month] & " And ImployeeID=" & [Forms]![SalesBySlsmDialog]![Salesman] & " And DealerID=" & [Forms]![SalesBySlsmDialog]![MfgCombo]) Is Null,0,CCur(DSum("Amount","All-Mfgs_All-Reps Query","Year=" & [Forms]![SalesBySlsmDialog]![BeginYear]-1 & " And MonthID<=" & [Forms]![SalesBySlsmDialog]![Month] & " And ImployeeID=" & [Forms]![SalesBySlsmDialog]![Salesman] & " And DealerID=" & [Forms]![SalesBySlsmDialog]![MfgCombo])))

How would I do a sum of each month seperately using a similar format as above for calculating the previous year?

 

[dhookom]

What is the Where Clause of your query? I would suggest removing the Year criteria from the where and placing it in the Column Headings like:

PIVOT "Yr" & [YearField]-[Forms]![SalesBySlsmDialog]![BeginYear] IN ("Yr0", "Yr1")



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NuggetInc]

Here is the where clause:

WHERE ((([All-Mfgs_All-Reps Query].MonthID)<=[Forms]![SalesBySlsmDialog]![Month]) AND (([All-Mfgs_All-Reps Query].Year)=[Forms]![SalesBySlsmDialog]![BeginYear]) AND (([All-Mfgs_All-Reps Query].ImployeeID)=[Forms]![SalesBySlsmDialog]![Salesman]) AND (([All-Mfgs_All-Reps Query].DealerID)=[Forms]![SalesBySlsmDialog]![MfgCombo]))

And I think you are on to something here, but I'm not quite sure I fully understand where your code example goes.
And how does it know to take the year the user entered, calculate the results, and then move to the next field, go back a year, and calculate the results for the previous year

 

[dhookom]

I would set the Column Headings to
ColHead: "Yr" & [YearField]-[Forms]![SalesBySlsmDialog]![BeginYear]
Set the Column Headings property to:
Column Headings: "Yr0", "Yr1"
This will create two crosstab columns with headings Yr0 and Yr1. These two columns will depend on the value in the form control.

This would allow you to remove the [red]AND (([All-Mfgs_All-Reps Query].Year)=[Forms]![SalesBySlsmDialog]![BeginYear])[/red] from your where clause.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NuggetInc]

dhookom,

I don't understand your line:

I would set the Column Headings to
ColHead: "Yr" & [YearField]-[Forms]![SalesBySlsmDialog]![BeginYear]

By column heading do you mean Field:?

and [YearField], what does that represent? (Do I replace that with the year field from my table, the year field from the form, ?? I'm confused and everything I tried didn't work.

Thanks for your help.

 

[dhookom]

By column heading do you mean Field:?
Yes, this would be in the top row of the query grid.

and [YearField], what does that represent?
This represents the name of your year field.

If you can't figure this out, post your SQL view.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NuggetInc]

I'm still having problems...heres the entire sql statement:

PARAMETERS [Forms]![SalesBySlsmDialog]![BeginYear] Long, [Forms]![SalesBySlsmDialog]![Month] Long, [Forms]![SalesBySlsmDialog]![Salesman] Long, [Forms]![SalesBySlsmDialog]![MfgCombo] Long;
TRANSFORM Sum([All-Mfgs_All-Reps Query].Amount) AS [The Value]
SELECT [All-Mfgs_All-Reps Query].Month, [All-Mfgs_All-Reps Query].MonthID
FROM [All-Mfgs_All-Reps Query]
WHERE ((([All-Mfgs_All-Reps Query].MonthID)<=[Forms]![SalesBySlsmDialog]![Month]) AND (([All-Mfgs_All-Reps Query].Year)=[Forms]![SalesBySlsmDialog]![BeginYear]) AND (([All-Mfgs_All-Reps Query].ImployeeID)=[Forms]![SalesBySlsmDialog]![Salesman]) AND (([All-Mfgs_All-Reps Query].DealerID)=[Forms]![SalesBySlsmDialog]![MfgCombo]))
GROUP BY [All-Mfgs_All-Reps Query].Month, [All-Mfgs_All-Reps Query].MonthID
ORDER BY [All-Mfgs_All-Reps Query].MonthID
PIVOT [All-Mfgs_All-Reps Query].Year;

 

[dhookom]

Try SQL of:
[tt][blue]
PARAMETERS [Forms]![SalesBySlsmDialog]![BeginYear] Long,
[Forms]![SalesBySlsmDialog]![Month] Long,
[Forms]![SalesBySlsmDialog]![Salesman] Long,
[Forms]![SalesBySlsmDialog]![MfgCombo] Long;
TRANSFORM Sum([Amount]) AS [The Value]
SELECT [Month], [MonthID]
FROM [All-Mfgs_All-Reps Query]
WHERE MonthID<=[Forms]![SalesBySlsmDialog]![Month] AND
ImployeeID=[Forms]![SalesBySlsmDialog]![Salesman] AND
DealerID=[Forms]![SalesBySlsmDialog]![MfgCombo]
GROUP BY [Month], MonthID
ORDER BY MonthID
PIVOT "Yr" & [Year]-[Forms]![SalesBySlsmDialog]![BeginYear] IN ("Yr0","Yr1");[/blue][/tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NuggetInc]

dhookom,

That worked great! Thanks for your help!

But I still don't really understand how that actually works. Is there some documentation out there that explains this. I have several access developers books and couldn't find what I was looking for.

Is there a process or name for what you did? Because I know I will be doing more of these, but they will obviously be variations on what was already done...

Thanks...

 

[lespaul]

That's some pretty SQL there Duane! have another star on me!!

les

 

[dhookom]

NuggetInc,
I haven't read this type of solution anywhere either so I'm not sure where you find the answers other than to ask the questions in forums. I know a couple of the Access book authors and have done some review of material to be published but haven't contributed other than a couple tips to Access SQL VB Advisor, Smart Access (anyone remember this?), and web sites.

There is no name for what I suggested. It just uses the functionality exposed in crosstabs. I published an FAQ in the reports forum that suggests how to do this with monthly column headings. It goes a bit deeper into how to display the month (year) in the column labels in a report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.