Crosstab Calculations

[jessabridge]

I need to compare 25-24. I tried doing this in my crossstab querry. I also tried importing my crosstab into a new querry and it did not work for either. I used =sum([25]-[24]) and it says

"subqueries cannot be used in the expression (sum([25]-[24]))"

This is my sql:
SELECT DISTINCTROW [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[Legacy True Account], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[Account Description], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[LAST NAME], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[FIRST NAME], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[JC Abbrv], Sum([q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[24]) AS 24, Sum([q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[25]) AS 25
FROM [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab]
GROUP BY [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[Legacy True Account], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[Account Description], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[LAST NAME], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[FIRST NAME], [q FY 2011 Non CO Straight OT Hrs Last 2 PP's_Crosstab].[JC Abbrv];


Any ideas?

Thanks!
Jessa

 

[PaulO]

Not sure I understood much of that ... based on not receiving any replies I may not be alone ... can you be a little clearer on what it is you are trying to achieve?

 

[jessabridge]

I am trying to compare 2 pay periods (PP24 & PP25).

my crosstab right now gives me:
Facility, Position, total of PP24 & PP25, PP24, PP25

I do not need a total of PP24 & PP25. I need a variance for PP24 & PP25, like PP25 minus PP24.

Does that make sense?
Jessa

 

[PaulO]

Is it adding P24 to P25 in the query i.e. [P24]+[P25] or getting that sum from a feeder query?

 

[jessabridge]

It is adding it on it's own. I tried removing the column, but whether it is in or not, I can not get a variance.

Jessa

 

[PaulO]

I was just wondering whether you could edit the expression that ADDS them to SUBTRACTING one from the other

 

[jessabridge]

Nop. Didn't work. Does not seem to recognize 24 and 25 as a column heading.