Crosstab Query - Column differences conflict

[JoshZulaica]

Hello, and thanks for reading, I'm new here.

I have a crosstab query with Date (dd/mm/yyyy) as columns, and serial numbers of financial instruments as rows. The total amounts (of holdings) are the Value.




What I need is to calculate the "daily net change", in order to do this, I have to substract the amount of each date by the amount of the previous date.

For example:
-----------03/01/2011-----04/01/2011-----07/01/2011
LD-110203----12000-----------12000---------14515
XA-110331-----1587------------1587---------1587
BI-110602------2112-------------0-------------0


Should turn out as:

-----------03/01/2011-----04/01/2011-----07/01/2011
LD-110203----12000------------0-------------2515
XA-110331-----1587------------0--------------0
BI-110602------2112----------2112-----------0


How can I accomplish this? I've tried a lot of things, and looked around on the internet but I have only found solutions to monthly net changes, since just assigning custom names to the columns like FebDiff: [Feb]-[Jan] is simple for that little number of columns.

(I'd like to note that the columns do not include all days in a year, only working days. So manually creating custom column headers is not really an option.. And if it helps in any way, I have a separate table in which I write the next date to be added to that list of dates.)

Thanks in advance for any help.

[June7]

You can build another query that references this query and calculate the differences within the query or use the crosstab as RecordSource for report and do calcs in textboxes. Problem is these field names will continually change so you would have to rebuild queries/reports. I don't see a solution.