average and sum cross query

Question

Hi

I ask before, but still any answer.......

is it possible with cross query to do this ? (in Access 2007)

I have this table:

50 | A1

60 | A1

70 | B1

80 | B1

90 | C1

I need to get this result:

A1 ------------------ B1 --------------- C1

sum | avg ----- sum | avg ----- sum | avg

55 | 110 --------- 75 | 130 ------ 90 | 90

I try to wright this:

TRANSFORM Sum(Worki.Value) AS XXsum
SELECT Worki.Name AS Name, Worki.Tdate AS Tdate, Worki.ID AS ID
FROM Worki
GROUP BY Worki.Name, Worki.Tdate, Worki.IDPIVOT Worki.Trit

How I can Get the average and the Sum ?

Answer 1

There are SQL functions SUM() and AVG() you can use. To get the sum of each of the types A1, B1, C1, you could use the following query:

SELECT Worki.ID, SUM(Worki.Value), AVG(Worki.Value) FROM Worki GROUP BY Worki.ID, Worki.Name, Worki.Tdate

Which returns

A1    110    55
B1    150    75
C1    90     90

Sorry I'm no help with the formatting

Answer 2

No, you can't do this with a crosstab query - you can only have one Value column in a crosstab. I think you'll have to build two queries - one for sum, one for average, then combine the information somehow. They will, at least, have the same columns.