Calculation in Cross Tab Query

[ps32208]

Hi,

I have created a cross tab query based on another query with the relevant fields which returns customers down the first column, months as column headings and sum of $ as the values.

I need to add a calculation field which works out month B less month A (variance) and does this for all months so C-B and D-C etc. Ideally I would end up with returned data which has a month column, then a variance column, then the next month etc...

Can anyone help?


Regards,
ps32208

 

[dhookom]

Is there a reason why you don't create your calculated values in your form or report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ps32208]

I do not know how to create such a calculation, as the number of columns of data returned will vary depending on the number of months of data. Eg if there were two consecutive months of data there would be one variance column and two variance columns if there were 3 months worth. Can you suggest a possible solution?

 

[dhookom]

Check this faq703-5466 in this forum to see how to create a report with dynamic date intervals. Set up your query/report to display a static number of months. You can then easily subtract [Mth1] from [Mth2] or whatever.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]