Multiple expressions (Sum, Count, Average) of same value in crosstab query

[Jon]

I have a report that is based on a crosstab query that displays the
total value of all purchases orders a given person has cut each month
(i.e., Sum(xBuyValTable.Value)). Now I need to add two more columns to
the report for each month and show the number of orders and their
average value. I've created the crosstab query for each value -
Count(xBuyValTable.Value) and Average(xBuyValTable.Value) - but how do
I join the three individual crosstab queries so the report can display
the result?

I've looked at the multiple columns trick that's been referred to here
(the demo uses the Northwind DB and creates Quantity and Quantity*Price
columns) and I don't think it'll work for my problem.

And for the sake of completeness, here's the Sum query:

TRANSFORM Sum(xBuyValTable.Value) AS SumOfValue
SELECT xBuyValTable.BuyerName, Sum(xBuyValTable.Value) AS [Total Of
Value]
FROM xBuyValTable
GROUP BY xBuyValTable.BuyerName
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

The Average query only has the first two lines different:

TRANSFORM Avg(xBuyValTable.Value) AS AvgOfValue
SELECT xBuyValTable.BuyerName, Avg(xBuyValTable.Value) AS [Total Of
Value]

And the Count query has these two lines:

TRANSFORM Count(xBuyValTable.Value) AS CountOfValue
SELECT xBuyValTable.BuyerName, Count(xBuyValTable.Value) AS [Total Of
Value]

 

[Michel Walsh]

Hi,


The easiest way is to join the 3 XTabs through their BuyerName field


SELECT xtb1.*, xtb2.*, xtb3.*
FROM xtb1 INNER JOIN xtb2 ON xtb1.BuyerName = xtb2.BuyerName
INNER JOIN xtb3 ON xtb1.BuyerName = xtb3.BuyerName


where xtb1, xtb2 and xtb3 are your 3 crosstabs. You can also concatenate
some fields together like:


SELECT xtb1.BuyerName, xtb1.Jan & "/" & xtb2.Jan & "/" & xtb3.Jan , ...
FROM xtb1 INNER JOIN ...



Hoping it may help,
Vanderghast, Access MVP

 

[Duane Hookom]

You can create multiple values in a single crosstab by combining a cartesian
and crosstab in one. There are instructions on how to do this at
http://www.access.hookom.net/ArtMultiValueXtab.htm.

 

[Jon]

Thanks - it looks like exactly what the doctor ordered. In fact, your
solution is so simple I'm merrily kicking myself for not thinking of
it.

Hi,


The easiest way is to join the 3 XTabs through their BuyerName field


SELECT xtb1.*, xtb2.*, xtb3.*
FROM xtb1 INNER JOIN xtb2 ON xtb1.BuyerName = xtb2.BuyerName
INNER JOIN xtb3 ON xtb1.BuyerName = xtb3.BuyerName


where xtb1, xtb2 and xtb3 are your 3 crosstabs. You can also concatenate
some fields together like:


SELECT xtb1.BuyerName, xtb1.Jan & "/" & xtb2.Jan & "/" & xtb3.Jan , ...
FROM xtb1 INNER JOIN ...



Hoping it may help,
Vanderghast, Access MVP

I have a report that is based on a crosstab query that displays the
total value of all purchases orders a given person has cut each month
(i.e., Sum(xBuyValTable.Value)). Now I need to add two more columns to
the report for each month and show the number of orders and their
average value. I've created the crosstab query for each value -
Count(xBuyValTable.Value) and Average(xBuyValTable.Value) - but how do
I join the three individual crosstab queries so the report can display
the result?

I've looked at the multiple columns trick that's been referred to here
(the demo uses the Northwind DB and creates Quantity and Quantity*Price
columns) and I don't think it'll work for my problem.

And for the sake of completeness, here's the Sum query:

TRANSFORM Sum(xBuyValTable.Value) AS SumOfValue
SELECT xBuyValTable.BuyerName, Sum(xBuyValTable.Value) AS [Total Of
Value]
FROM xBuyValTable
GROUP BY xBuyValTable.BuyerName
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

The Average query only has the first two lines different:

TRANSFORM Avg(xBuyValTable.Value) AS AvgOfValue
SELECT xBuyValTable.BuyerName, Avg(xBuyValTable.Value) AS [Total Of
Value]

And the Count query has these two lines:

TRANSFORM Count(xBuyValTable.Value) AS CountOfValue
SELECT xBuyValTable.BuyerName, Count(xBuyValTable.Value) AS [Total Of
Value]