cartesian/crosstab query problem

[sabre30]

Hi,

I'm SO close, but I have a problem with a crosstab/cartesian product query that I based on Duane Hookum's example. Here's what I have:

TRANSFORM sum(iif([Fldname]="Balance",[Balance],[Return])) AS TheVal
SELECT rptBF_Custom5.PolicyNumber, rptBF_Custom5.IAName, rptBF_Custom5.TPMName
FROM rptBF_Custom5, tblBF_Custom5_Fields
GROUP BY rptBF_Custom5.PolicyNumber, rptBF_Custom5.IAName, rptBF_Custom5.TPMName
PIVOT [FldName] & rptBF_Custom5.Period;

What I need is the Sum([Balance]) or the Avg([Return]) as the value.

I'll probably kick myself when I see the answer, but I'm at a loss! TIA!
Joanne

 

[PHV]

HAve you tried this ?
TRANSFORM IIf([Fldname]="Balance",Sum([Balance]),Avg([Return])) AS TheVal

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sabre30]

Thanks, yes I tried that and this is the error I get:

"You tried to execute a query that does not include the specified expression as part of an aggregate function."

 

[sabre30]

I didn't think this would work in a crosstab, but it did. I just added the IIF statement to the group by clause.

TRANSFORM iif([Fldname]="Balance",[Balance],[Return]) AS TheVal
SELECT rptBF_Custom5.PolicyNumber, rptBF_Custom5.IAName, rptBF_Custom5.TPMName
FROM rptBF_Custom5, tblBF_Custom5_Fields
GROUP BY rptBF_Custom5.PolicyNumber, rptBF_Custom5.IAName, rptBF_Custom5.TPMName, iif([Fldname]="Balance",[Balance],[Return])
PIVOT rptBF_Custom5.Period & [FldName];