I have a query which outputs the following:

IDM Month Account Location RAmnt FAmnt Var1 BAmnt Var2
--------------------------------------------------------------------
1 November 63410 Montreal 1500 1750 -250 1600 -100
2 December 63410 Montreal 1750 1750 0 1750 0
3 January 63410 Montreal 1600 1650 -50 1500 100
4 Febuary ... ... ... ... ... ... ...
5 March ... ... ... ... ... ... ...
6 April ... ... ... ... ... ... ...
7 May ... ... ... ... ... ... ...
8 June ... ... ... ... ... ... ...
9 July ... ... ... ... ... ... ...
10 August ... ... ... ... ... ... ...
11 September ... ... ... ... ... ... ...
12 October ... ... ... ... ... ... ...

I dont display the IDM, Month, Account and Location columns so I just
keep RealAmount, ForecastedAmount, Variation1, BudgetedAmount and
Variation2.

This is exactly the data I want. BUT I want to flip it over si I get
my 12 months as columns and RealAmount, ForecastedAmount, Variation1,
BudgetedAmount and Variation2 as rows.

I tried to make a crosstab query but didnt succedded to get what I
want.

I read on TRANSFORM PIVOT clause but I dont really understand the
TRANSFORM part since my data doesnt need to be transformed.

The only way I could make something looking like I wanted was by using
a Pivot Table (access object) inserted in a form getting its data from
an access query. But it brings new problems. First, it is not updated
automatically if the query result it's bound to changes. Second, I
dont know whete to change the data source for the pivot table. I would
like to be able to copy a form with a Pivot Table in it and just
change the data source of the pivot table to an other query.

Hope someone can help me. This is the toughest Access problem I've
been facing.

Thanks,

Martin

it appears that your data is formatted the wrong way for what you want
to do. the dataset below looks like a spreadsheet, and for
flexibility, it should be formatted more like:

i'm not sure what IDM is, but it looks like the relationship to
something else. you should also have a key.

the TRANSFORM statement does not transform data, it re-arranges it.
with a table set up as above, you can use either the months (as below)
or the Type as column headers (or even the Location or Account). the
amounts can be summarized in any fasion.

hope this helps. tom

l0x_ (AT) msn (DOT) com (mdeschen) wrote in message news:<fa59c606.0307221103.bb5d60a (AT) posting (DOT) google.com>...