Transpose table for report presentation

  • Thread starter Thread starter Mishanya
  • Start date Start date
M

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).
 
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
 
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
 
Back
Top