UNION or Multiple crosstabs in one?

  • Thread starter Thread starter Amin
  • Start date Start date
A

Amin

Hello Experts,

I've imported two tables from our financial suit, I need to display the
sales report in a special layout.

Tables & Fields:

[Customer_List]
-ListID
-Name
-Customer Type

[Invoices]
-Customer.ListID
-RefNum
-TXNDate
-Item
-Rate
-Amount

Report Layout:

Customer: CustomerA
Item | January |... |December|
ItemA |Sum(Qty),Sum(Amount),Avg(Rate)|...
ItemB |Sum(Qty),Sum(Amount),Avg(Rate)|...

While I can create a cross tab query for each element (Qty, Amount,Rate) but
I need some way to combine them all in one sheet.

I'm open to any suggestions and my skill level is intermediate.

Thank you in advance, you've been all a great help in the past.

Amin
 
Well, I believe you can use a calculated field like the following as the VALUE
field for the crosstab.

TRANSFORM Sum(Qty) & "," & Sum(Amount) & "," & Avg(Rate)
SELECT Customer_List.[Name]
FROM Customer_List INNER JOIN Invoices
ON Customer_List.ListID = Invoices.ListID
GROUP BY Customer_List.[Name]
PIVOT Format(TXNDate,"mmmm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
There is a solution for creating multiple valued crosstabs at
http://www.tek-tips.com/faqs.cfm?fid=4524.

--
Duane Hookom
Microsoft Access MVP


John Spencer MVP said:
Well, I believe you can use a calculated field like the following as the VALUE
field for the crosstab.

TRANSFORM Sum(Qty) & "," & Sum(Amount) & "," & Avg(Rate)
SELECT Customer_List.[Name]
FROM Customer_List INNER JOIN Invoices
ON Customer_List.ListID = Invoices.ListID
GROUP BY Customer_List.[Name]
PIVOT Format(TXNDate,"mmmm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello Experts,

I've imported two tables from our financial suit, I need to display the
sales report in a special layout.

Tables & Fields:

[Customer_List]
-ListID
-Name
-Customer Type

[Invoices]
-Customer.ListID
-RefNum
-TXNDate
-Item
-Rate
-Amount

Report Layout:

Customer: CustomerA
Item | January |... |December|
ItemA |Sum(Qty),Sum(Amount),Avg(Rate)|...
ItemB |Sum(Qty),Sum(Amount),Avg(Rate)|...

While I can create a cross tab query for each element (Qty, Amount,Rate) but
I need some way to combine them all in one sheet.

I'm open to any suggestions and my skill level is intermediate.

Thank you in advance, you've been all a great help in the past.

Amin
Click to expand...
Click to expand...
 
Hi John,

Thanks for your response, where the query run fine I've encountered 3
problems:

1- looks like you forgot about the ITEMS row for each customer (kindly
review my report layout) looks like it's only showing the result for the
first item.

2- only 5 months showed in the columns and they were not arranged (April,
Feb, Jan, March, May) I prefer to see the whole year if it's empty (not
important though)

3- The Avg section shows MANY decimals, is it possible to truncate it to 2?

Appreciate your help.

Amin

John Spencer MVP said:
Well, I believe you can use a calculated field like the following as the VALUE
field for the crosstab.

TRANSFORM Sum(Qty) & "," & Sum(Amount) & "," & Avg(Rate)
SELECT Customer_List.[Name]
FROM Customer_List INNER JOIN Invoices
ON Customer_List.ListID = Invoices.ListID
GROUP BY Customer_List.[Name]
PIVOT Format(TXNDate,"mmmm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello Experts,

I've imported two tables from our financial suit, I need to display the
sales report in a special layout.

Tables & Fields:

[Customer_List]
-ListID
-Name
-Customer Type

[Invoices]
-Customer.ListID
-RefNum
-TXNDate
-Item
-Rate
-Amount

Report Layout:

Customer: CustomerA
Item | January |... |December|
ItemA |Sum(Qty),Sum(Amount),Avg(Rate)|...
ItemB |Sum(Qty),Sum(Amount),Avg(Rate)|...

While I can create a cross tab query for each element (Qty, Amount,Rate) but
I need some way to combine them all in one sheet.

I'm open to any suggestions and my skill level is intermediate.

Thank you in advance, you've been all a great help in the past.

Amin
Click to expand...
Click to expand...
 
Thank you John, I've firgured it out .. they were small glitches

John Spencer MVP said:
Well, I believe you can use a calculated field like the following as the VALUE
field for the crosstab.

TRANSFORM Sum(Qty) & "," & Sum(Amount) & "," & Avg(Rate)
SELECT Customer_List.[Name]
FROM Customer_List INNER JOIN Invoices
ON Customer_List.ListID = Invoices.ListID
GROUP BY Customer_List.[Name]
PIVOT Format(TXNDate,"mmmm")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello Experts,

I've imported two tables from our financial suit, I need to display the
sales report in a special layout.

Tables & Fields:

[Customer_List]
-ListID
-Name
-Customer Type

[Invoices]
-Customer.ListID
-RefNum
-TXNDate
-Item
-Rate
-Amount

Report Layout:

Customer: CustomerA
Item | January |... |December|
ItemA |Sum(Qty),Sum(Amount),Avg(Rate)|...
ItemB |Sum(Qty),Sum(Amount),Avg(Rate)|...

While I can create a cross tab query for each element (Qty, Amount,Rate) but
I need some way to combine them all in one sheet.

I'm open to any suggestions and my skill level is intermediate.

Thank you in advance, you've been all a great help in the past.

Amin
Click to expand...
Click to expand...
 
Back
Top