Crosstab Query – multiple row results

[barrysprout]

I would like to create a report in a crosstab format that shows sales per month for a range of products but for each product I want the current year row to be followed by the previous year row like so:

Jan Feb Mar ….. etc
Product A
2007 100 150 200 …..etc
2006 80 120 150 …..etc

Product B
2007
2006

etc

Can this be done and if so how? Any ideas much appreciated.

 

[dhookom]

Where is your problem? Can you get the appropriate data out of the crosstab? You should only need to set your sorting and grouping your report to Product and then Year descending.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[barrysprout]

That would be fine if I was dealing with a finite dataset but I will be adding data each month which means I would have to keep modifying the report every time to include a field for the new month. I want to create a report that will give 3 years data whenever it is run. It seems to me a case of combining two or more different queries in the same group.

 

[dhookom]

Enter all possible months (column headings) into the column headings property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[barrysprout]

That would work but would still be limited because you have to enter the years in the column headings otherwise the data from all years will be summed for each month. Maybe I'm just missing something ...

 

[lespaul]

If you are grouping by year (in your rows) then why would you also need the year in the header?

Jan Feb Mar Apr May June etc.
2005
2006
2007

that was your example above right?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[barrysprout]

Not quite. I need to show 3 year's data for each product like so:

Product A Jan Feb Mar etc...
2007
2006
2005

Product B
2007
2006
2005

etc.

The grouping is on the product.

 

[barrysprout]

I was missing something. I needed to include another row heading with the year while using Jan to Dec as the column headings. Thanks for your help, everyone.

 

[dhookom]

You can have many row headings in a crosstab. Create one based on your year calculation.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]