crosstab query / pivot table 1

[DevelopV]

On a subform I want to display sales history as a crosstab query or a pivot table.
The user will select a product in a listbox on the main form and in the subform I want to display the sales history as follows:
Months on the year as rows
Years as columns
Sum of product sales as data

I always want to limit the sales history to the last 3 years. The year range is from Jan - Dec.

The data come from the following:
tblInvoice - InvoiceDate
tblInvoiceProduct - ProductId, Quantity
The two tables have a one-many relationship with field InvoiceId

What is the best method to accomplish this?

Many thanks in advance

 

[DevelopV]

I also want all the years and months shown, irrespective if they have data or not

 

[SkipVought]

Then you must outer join to a table that you may need to create, that has ALL the dates you want displayed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DevelopV]

That makes sense for "months" as row headings but I don't think it will work for column headers

I want the last 3 years as column headers so it would currently show 2012, 2011, 2010.
Next year is will be 2013, 2012, 2011 .....

 

[dhookom]

I would create columns based on an expression of:
"Y" & Year(Date()) - Year(InvoiceDate)
Set the column headings property to "Y0","Y1","Y2"
Y0 will display the current year and Y2 will be two years ago.

Duane
Hook'D on Access
MS Access MVP

 

[DevelopV]

I really do appreciate all the assistance offered.
Can someone offer me a complete solution?

Many, many thanks

 

[dhookom]

Assuming tblInvoice also contains the field InvoiceID, I would begin by creating this crosstab query:

TRANSFORM Sum(tblInvoiceProduct.Quantity) AS SumOfQuantity
SELECT tblInvoiceProduct.ProductID, Month([InvoiceDate]) AS Mth
FROM tblInvoice INNER JOIN tblInvoiceProduct ON tblInvoice.InvoiceID = tblInvoiceProduct.InvoiceID
GROUP BY tblInvoiceProduct.ProductID, Month([InvoiceDate])
PIVOT "Y" & Year(Date())-Year([InvoiceDate]) In ("Y2","Y1","Y0");

Use this query as the record source of a subform and set the Link Master/Child properties to the listbox and ProductID. If you aren't getting all of the months for each product, you can create a cartesian query that includes all months and products like:

SELECT Month([InvoiceDate]) AS Mth, tblInvoiceProduct.ProductID
FROM tblInvoice, tblInvoiceProduct
GROUP BY Month([InvoiceDate]), tblInvoiceProduct.ProductID;

Join the crosstab with the cartesian so every record from the cartesian is included.


Duane
Hook'D on Access
MS Access MVP

 

[DevelopV]

Many, many thanks

How do I join the crosstab with the cartesian so every record from the cartesian is included.

 

[DevelopV]

also how can I reverse the year order?
The results are currently 2011,2012,2013
I would like them as 2013,2012,2011

Again, many thanks

 

[dhookom]

Again, create a new query using the following.
"Join the crosstab with the cartesian so every record from the cartesian is included."
This is a matter of double-clicking the join line and choosing the correct option.

You can set the order of the columns in the subform design.

Duane
Hook'D on Access
MS Access MVP