Need to calc Min in crosstab query with Zero field

[svobfamily]

I have a cross tab query with scores in rows that may contain 0 based on a Null value. I used Null: IIf(Sum([Score]) Is Null,0,Sum([Score])) to convert the Null to 0

When I add a Min to the query, it leaves out the 0 scores and just returns the lowest non-0 number as the Min. I think this is because the Null is not part of the Total Score calc?



The idea is to drop the lowest score from a series of matches (which would include a zero score).

TRANSFORM IIf(Sum([Score]) Is Null,0,Sum([Score])) AS [Null]
SELECT AR_Sporter_Panhandle_Best.[Display Name], AR_Sporter_Panhandle_Best.Team, AR_Sporter_Panhandle_Best.[Skill Level], Sum(AR_Sporter_Panhandle_Best.Score) AS [Total Of Score]
FROM AR_Sporter_Panhandle_Best
GROUP BY AR_Sporter_Panhandle_Best.[Display Name], AR_Sporter_Panhandle_Best.Team, AR_Sporter_Panhandle_Best.[Skill Level]
PIVOT AR_Sporter_Panhandle_Best.Hometown;

Is there a way to do this? Right now I have to export the crosstab to excel, add Min column and then import it back in for a report.

thankyou!!

[ranman256]

you didnt need the IIF,
a NZ(field) will convert.

[NTC]

in addition to 256's input; consider performing your 0 conversion and your other data calculations in the normalized data before cross tab. Cross tab is presentation/display and the last step of a process typically.