CrossTab - insert a calculated field

[weiguo.shi]

Hello,

I met a problem in ACCESS 2010:
I have a PivotTable and I want to insert a field to calculate (for example) the difference between 2 values ​​in this table: eg
___________ 2007___2008___ Difference
Project 1_____200____218__________ 18
Project 2_____150____118_________ -32
Project 3____1520___ 1720________ 200
Is it possible?
Does anyone could give some advices?
Thank you in advance.

Weiguo

 

[apr pillai]

You may use the Cross-tab Query as source for a SELECT Query and create a new Column for calculation between transformed fields in the cross-tab query.

 

[plog]

Create another query using that crosstab query as the source:

SELECT ProjectNameField, [2007], [2008], ([2008]-[2007]) AS Difference
FROM CrossTabQueryName;

A couple problems with this might be-no field name called [2007] or [2008] and Access can sometimes puke when running a query that uses a crosstab query as a source. That depends on how much data is in the underlying table of the cross-tab query.

 

[weiguo.shi]

You may use the Cross-tab Query as source for a SELECT Query and create a new Column for calculation between transformed fields in the cross-tab query.

Thank you for your reply.

But the value I got is grouped by date, thus if I use this as a sub-query, I could not get the value '200' for example. And if I want to calculate the difference between every two weeks, what should I do? The query will update every week.

Thank you again.

 

[weiguo.shi]

Create another query using that crosstab query as the source:

SELECT ProjectNameField, [2007], [2008], ([2008]-[2007]) AS Difference
FROM CrossTabQueryName;

A couple problems with this might be-no field name called [2007] or [2008] and Access can sometimes puke when running a query that uses a crosstab query as a source. That depends on how much data is in the underlying table of the cross-tab query.

Thank you for your reply.

But the value I got is grouped by date, thus if I use this as a sub-query, I could not get the value '200' for example. And if I want to calculate the difference between every two weeks, what should I do? The query will update every week.

Thank you again.

 

[weiguo.shi]

Excuse-me, I made a mistake, it is a Pivot Table, not a Crosstab.

 

[Alansidman]

CrossPost solution at

http://www.accessforums.net/queries/crosstab-insert-calculated-field-13627.html#post59727

Alan

 

[weiguo.shi]

[QUOTE
Alan[/QUOTE]

thank you, Alan