Pivot Table

[Jose]

I'm trying building a pivot table in ms access 2007 but it doesn't sum the
values for each month.



Imagine the following query (simple query)

Month City Sell

January Lisbon 10

January Porto 12

January Lisbon 15

January Funchal 13

January Lisbon 10

January Porto 10

January Funchal 20





In excel when I create a pivot table, it sum the values of sells and for
each month it shows only one row.



Lisbon Porto Funchal

January 35 22 33



In ms access it doesn't sum the values, it shows the following values:

Lisbon Porto Funchal

January 10 12 13

15 10 20

10



There is way to sum the values in pivot table in access?

Programmatically is it possible?



Thanks a lot for any suggestion



JCP

 

[RonaldoOneNil]

In query design, put in your 3 columns - Month as Group By and Row Heading,
City as Group By and Column Heading, Sales Figure as Sum and Value.

The SQL for this is
TRANSFORM Sum([Sales Figures].Sell) AS SumOfSell SELECT [Sales
Figures].MonthText FROM [Sales Figures] GROUP BY [Sales Figures].MonthText
PIVOT [Sales Figures].City;