CrossTab - insert a calculated field

[weiguo.shi]

Hello,

I met a problem in ACCESS 2010:
I have a CrossTab 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

[alansidman]

In the difference field in the QBE, have you tried putting an expression there? ie.

Difference:  [2008]-[2007]

What happens when you insert the above?

Alan

[weiguo.shi]

In the difference field in the QBE, have you tried putting an expression there? ie.

Difference:  [2008]-[2007]

What happens when you insert the above?

Alan

thank you for your reply.
but if I want to calculate the difference between every two weeks, what should I do? My query will update every week.
Thank you again.

[weiguo.shi]

In the difference field in the QBE, have you tried putting an expression there? ie.

Difference:  [2008]-[2007]

What happens when you insert the above?

Alan

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

[alansidman]

What is the source of your pivot table? A query? If a query, follow my previous instructions and then bring the calculation into the PT. I do not use PTs in Access. If I have a need for a PT, I prefer to export my data to Excel and use the PT functionality in Excel which I believe offers greater flexibility.
Alan

[weiguo.shi]

What is the source of your pivot table? A query? If a query, follow my previous instructions and then bring the calculation into the PT. I do not use PTs in Access. If I have a need for a PT, I prefer to export my data to Excel and use the PT functionality in Excel which I believe offers greater flexibility.
Alan

Thank you.
It is a query.
If I want to calculate the difference between two weeks, what should I do?
Thank you very much!

[weiguo.shi]

What is the source of your pivot table? A query? If a query, follow my previous instructions and then bring the calculation into the PT. I do not use PTs in Access. If I have a need for a PT, I prefer to export my data to Excel and use the PT functionality in Excel which I believe offers greater flexibility.
Alan

That is to say that there is no way like "Difference from" in Excel which is to calculate directly the difference between two values?

[alansidman]

Need more information. Perhaps you should explain what your table(s) look like. Field names and data types. Then post your SQL statement. Without more data, I am only guessing at a solution on how to compare data between two weeks.

[weiguo.shi]

Need more information. Perhaps you should explain what your table(s) look like. Field names and data types. Then post your SQL statement. Without more data, I am only guessing at a solution on how to compare data between two weeks.

Thank you very much.
My pivot table is something like this, I haven't succeeded in quoting an image here, I am sorry.

2009
Jan
week 1 week 2 week 3 week 4 week 5
A 10 2 0 14 11
B 16 15 0 21 2
C 1 23 6 9 11
D 0 15 5 4 0
E 4 3 4 2 9
Total 31 58 15 50 33

I want to add a field 'Difference between every two weeks'.P.E:between week 1 and week 2, I want to add a filed like "Difference -8 -1 22 15 -1 27 "
In my query, I just grouped the dates like "11/04/2009" and counted the nombers. The weeks in the pivot table are obtained automaticly by Access.

[alansidman]

I don't have Access 2010, but in 2007, with your pivot table open, click on design tab on the ribbon. On the Tools section, there icon for Formula. Click on this and you should be able to insert caluclation fields. I think that will be your solution. Additionally, you could do your calculations in your query and carry the results over to the PT.

Alan

[weiguo.shi]

I don't have Access 2010, but in 2007, with your pivot table open, click on design tab on the ribbon. On the Tools section, there icon for Formula. Click on this and you should be able to insert caluclation fields. I think that will be your solution. Additionally, you could do your calculations in your query and carry the results over to the PT.

Alan

Thank you.
I think my problem is how to define the formule of calculation, because the 'weeks' are arranged automaticly by Access, so I don't have the choice of 'weeks' in calculation field. May be I try to calculate during the query.