Transpose table for report presentation

[Mishanya]

Been reading a lot on the issue, but still unable to figure.
I have a table (the names are simplified for a matter of example):

BalanceID BalanceDate Income Expence
1 30/01/2008 1000 700
2 30/02/2008 70 200
3 30/03/2008 300 0
.... ... ... ...
.... ... ... ...

I need to present the data in report as follows:

BalanceDate 30/01/2008 30/02/2008 30/03/2008
Income 1000 70 300
Expence 700 200 0
Total 300 -130 300

Tried to play with UNION and crosstabs, but did not succeed.

Will appriciate any help (exept for totals, which I can produce).

 

[Arvin Meyer [MVP]]

Look at the Partition() Function with a CrossTab query. You will need to run
your Totals query first, then use that with a CrossTab and the Partition()
Function. I can't find current help files so I copied the files from Access
97 help and put them on my website:

http://www.accessmvp.com/PartitionFunctionHelp.txt

Hope this helps

 

[Mishanya]

Hi Arvin.
I'm not sure you've understood my intention.
I do not intend to run any totals query - I simply calculate them in unbound
textboxes in the report-footer.
To simplify the issue - I just need to transpose the table:

BalanceID BalanceDate Income Expense
1 30/01/2008 1000 700
2 30/02/2008 70 200
3 30/03/2008 300 0

into report:

BalanceDate 30/01/2008 30/02/2008 30/03/2008
Income 1000 70 300
Expense 700 200 0

 

[Arvin Meyer [MVP]]

As long as you do not exceed a reasonable column count, you should be able
to use the Partition() function with a CrossTab query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com