Crosstab query calculation for each pair of columns

[wvandenberg]

I was wondering if this is even possible before I keep trying to make it work.

I have a crosstab query that looks like this (I left out some of the columns for simplicity as you will see from the SQL):

Parameter       NC23 (May-30)  NC23D (May-30)  SC16 (Jun-01)  SC16D (Jun-01)
Colour          6.7            5.5             -5             -5
Conductivity	1660	       1650            704           707
TDS             910            1040            370           370
Turbidity       1              0.61           0.4            0.82
Chloride      48.8             49.2            16.9            17
Sodium        19               18            20.8            20.8
NO2+NO3       -0.07            -0.07           1.11         1.11
Ammonia       0.39             0.54            -0.05        -0.05
E. coli       -1                -1             -1           -1
Tot Coliforms  -1                -1             -1           -1
Fec Coliforms  -1                -1             -1           -1

The SQL is:

TRANSFORM Max(qryQADupsPaired.ResultValue) AS MaxOfResultValue
SELECT qryQADupsPaired.Round, qryQADupsPaired.GroupDescription AS [Group], qryQADupsPaired.AnalyteName AS Parameter, qryQADupsPaired.ReportingUnitsDescription AS Units, qryQADupsPaired.DL AS [Method Detection Limit]
FROM qryQADupsPaired
WHERE (((qryQADupsPaired.Round)=2))
GROUP BY qryQADupsPaired.Round, qryQADupsPaired.GroupDescription, qryQADupsPaired.AnalyteName, qryQADupsPaired.ReportingUnitsDescription, qryQADupsPaired.DL, qryQADupsPaired.GGOrder, qryQADupsPaired.GAOrder
ORDER BY qryQADupsPaired.GGOrder, qryQADupsPaired.GAOrder
PIVOT [SampleName] & " (" & Format([SampleDate],"mmm-dd") & ")";

So, what i would like to do is a simple calculation ((max-min)/min) for each pair of columns so the output would look like this:

Parameter      NC23 (May-30)   NC23D (May-30)   % diff   SC16 (Jun-01)   SC16D (Jun-01)   % diff
Colour         6.7             5.5              22%      -5              -5               -
Conductivity   1660            1650             1%       704             707              0%
TDS            910             1040             14%      370             370              0%
Turbidity      1               0.61             64%      0.4             0.82             105%
Chloride       48.8            49.2             1%       16.9            17               1%
Sodium         17              18               6%       20.8            20.8             0%
NO2+NO3        -0.07           -0.07            -        1.11            1.11             0%
Ammonia        0.39            0.54             38%      -0.05           -0.05            -
E. coli        -1              -1               -        -1              -1               -
Tot Coliforms  -1              -1               -        -1              -1               -
Fec Coliforms  -1              -1               -        -1              -1               -

Is this possible, and if so, how? Any comments are appreciated.

Thanks,
Wendy

 

[vbahelp07]

first thing that comes to mind is to do another query from the results of this and insert a column for each unique pair for the calculation you want for each pair.

sorry, that's the first thing that comes to mind with the cross-tab columns values