Sum Across Columns in Crosstab

[zmbd]

The question follows; however, a little setup:

Table_Order
[Order_PK]
[etc...]

Table_S
[s_pk] auto
[fk_Order]long - related to table_order
[s_name]text
[s_date]date as YYYY-MM-DD hh:nn:ss
[...]

Table_analyte
[analyte_pk]auto
[analyte_name]text

Table_R
[R_pk]auto
[fk_s]long - related to table_s
[fk_analyte]long - related to table_analyte
[r_value]double

--
Data
Table_S (note start of business and end of business is mentioned here for use in a related thread)

[S_pk][s_name][s_date             ]
[1]   [s_1   ][2016-05-01 07:00:00] '(start of business day 1)
[2]   [s_2   ][2016-05-01 00:01:01]
[3]   [s_3   ][2016-05-02 06:59:59] '(END of business day 1)
[4]   [s_4   ][2016-05-02 07:00:00] '(start of business day 2)

Table_analyte

[analyte_pk][analyte_name]
[1         ][analyte_A   ]
[2         ][analyte_B   ]
[3         ][analyte_C   ]
[4         ][analyte_D   ]

Table_R

[R_PK][fk_s][fk_Analyte][r_value]
[1   ][1   ][1         ][100    ]
[2   ][1   ][2         ][200    ]
[3   ][1   ][3         ][300    ]
[4   ][1   ][4         ][400    ]
[5   ][2   ][1         ][102    ]
[6   ][2   ][2         ][202    ]
[7   ][2   ][3         ][302    ]
[8   ][2   ][4         ][402    ]
[etc... for fk_s=3>N]

---

So the crosstab query does a wonderful job for me until now

[S_Name][analyte_A   ][analyte_B   ][analyte_C   ][analyte_D   ]
[S_1   ][100         ][200         ][300         ][400         ]
[S_2   ][101         ][201         ][301         ][401         ]
[S_3   ][111         ][211         ][311         ][411         ]
[etc...]

Now I need sum(analyte_A analyte_B) and add this column (name it [A+B]) to the crosstab and it's driving me nuts.

[S_Name][A+B   ][analyte_A   ][analyte_B   ][analyte_C   ][analyte_D   ]
[S_1   ][300   ][100         ][200         ][300         ][400         ]
[S_2   ][302   ][101         ][201         ][301         ][401         ]
[etc...]

What I tried was a modification of Allen's method (http://allenbrowne.com/ser-67.html) by creating query against Table_analyte where [analyte_pk]=1 and 2
Using that as the Cartesian product table and tried my summation...

I tried just a table with a single field and actually two records (I actually need to Sum(A,B) and Sum(A,C))
Table_sumationv alues
[A+B]
[A+C]
and two calculated fields in the crosstab as shown in AB article; however, I just couldn't get the conditional straight...

The database has thousands of samples and associated analyte/result entries....

In the past I've just exported the crosstab over to excel and manually inserted the columns I need and the sum() equation; however, I would really like to automate this so that I can go straight from the crosstab > Report > Emailed PDF

I know this is something simple I've missed.
My thought here is that I need to add these to the

[analyte_pk][analyte_name]
[1         ][analyte_A   ]
[2         ][analyte_B   ]
[3         ][analyte_C   ]
[4         ][analyte_D   ]
[5         ][A+B         ]
[6         ][A+C         ]

Cartesian product in the CTQ
then (air code here...)

Sum(IIf([FieldName]="A+B",
   (iif(analyte_pk=1,
      [r_value],0)
      +iif(analyte_pk=2,
      [r_value],0))
[r_value])

or something along those lines....
-Z

[zmbd]

Ok, as I'm working thru this,
Built an aggregate query against the table_r pulling in the [fk_s] and grouping on this field and building a calculated field

[A_B]:
   SUM(IIF([fk_analyte]=1,[r_value],0)
     +IIF([fk_analyte]=2,[r_value],0))

set this as an expression in the AQry.
This at least calculated the value for A+B

Added this AQry to the CTQ and left join on the primary table in the CTQ

Added "A+B" to the table_analyte

Used Allen's thoughts on the calculated field

SUM(Switch([analyte_name]="A+B",[AQry]![A_B],
    [analyte_name]<>"A+B",[Qry_SRslts]![R_Value]))

This seems to be an awful Kludge but it seems to be working, amazingly fast given how large the data table is; however, I can for-see this to be a nightmare to maintain down the road if we need to add other custom calculations.

Better thoughts?

-z