Eliminate 0 value in crosstab

[Rbirdie]

I have a data dump (over 110k info) that is now set up as 3 crosstab queries.
I then have a query to join them all together to see all informaition needed.
The problem I am having is there are some 00 values that are causing my AVG's
to total instead. I realize that this is a normalizing issue, but this data
is coming off of mainframe and so it is what it is. So, if I can eliminate
the zeros or tell the query to ignore them without eliminating the row that
would be great. Any ideas?

 

[KARL DEWEY]

Post your SQL.

 

[Rbirdie]

SELECT DISTINCTROW Crosstab1.Model,
Avg((NZ(Crosstab2.Actual,0)+NZ(Crosstab2.COPIED,0)+NZ(Crosstab2.[HIGH PRIOR
PRORATE],0)+NZ(Crosstab2.[Prorated From Actual],0))) AS [Total Count of
Actuals],
Sum((NZ(Crosstab1.Actual,0)+NZ(Crosstab1.COPIED,0)+NZ(Crosstab1.[HIGH PRIOR
PRORATE],0)+NZ(Crosstab1.[Prorated From Actual],0))) AS [Avg of Actuals],
Crosstab1.Actual, Crosstab1.COPIED, Crosstab1.[HIGH PRIOR PRORATE],
Crosstab1.[Prorated From Actual],
NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0) AS [otal
Count of Defaults], Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN,
Crosstab1.CountOfModel, Crosstab3.[<>], Crosstab3.[Accrual Cleanup],
Crosstab3.EXCEL, Crosstab3.MAIL, Crosstab3.[PBMS READINGS],
Crosstab3.PRORATION, Crosstab3.UNKNOWN, Crosstab3.VOICE, Crosstab3.WEBSITE,
Crosstab3.ICR
FROM (Crosstab1 INNER JOIN Crosstab2 ON Crosstab1.Model = Crosstab2.Expr1)
INNER JOIN Crosstab3 ON Crosstab2.Expr1 = Crosstab3.Model
GROUP BY Crosstab1.Model, Crosstab1.Actual, Crosstab1.COPIED,
Crosstab1.[HIGH PRIOR PRORATE], Crosstab1.[Prorated From Actual],
NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0),
Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN, Crosstab1.CountOfModel,
Crosstab3.[<>], Crosstab3.[Accrual Cleanup], Crosstab3.EXCEL, Crosstab3.MAIL,
Crosstab3.[PBMS READINGS], Crosstab3.PRORATION, Crosstab3.UNKNOWN,
Crosstab3.VOICE, Crosstab3.WEBSITE, Crosstab3.ICR, Crosstab2.[Prorated From
Default], Crosstab2.UNKNOWN, Crosstab2.Actual, Crosstab2.COPIED,
Crosstab2.[HIGH PRIOR PRORATE], Crosstab2.[Prorated From Actual];

 

[Rbirdie]

 

[Rbirdie]

 

[KARL DEWEY]

Try replacing
NZ(Crosstab2.Actual,0)
with
IIf([Crosstab2].[Actual]=0,Null,[Crosstab2].[Actual])
--
KARL DEWEY
Build a little - Test a little

SELECT DISTINCTROW Crosstab1.Model,
Avg((NZ(Crosstab2.Actual,0)+NZ(Crosstab2.COPIED,0)+NZ(Crosstab2.[HIGH PRIOR
PRORATE],0)+NZ(Crosstab2.[Prorated From Actual],0))) AS [Total Count of
Actuals],
Sum((NZ(Crosstab1.Actual,0)+NZ(Crosstab1.COPIED,0)+NZ(Crosstab1.[HIGH PRIOR
PRORATE],0)+NZ(Crosstab1.[Prorated From Actual],0))) AS [Avg of Actuals],
Crosstab1.Actual, Crosstab1.COPIED, Crosstab1.[HIGH PRIOR PRORATE],
Crosstab1.[Prorated From Actual],
NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0) AS [otal
Count of Defaults], Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN,
Crosstab1.CountOfModel, Crosstab3.[<>], Crosstab3.[Accrual Cleanup],
Crosstab3.EXCEL, Crosstab3.MAIL, Crosstab3.[PBMS READINGS],
Crosstab3.PRORATION, Crosstab3.UNKNOWN, Crosstab3.VOICE, Crosstab3.WEBSITE,
Crosstab3.ICR
FROM (Crosstab1 INNER JOIN Crosstab2 ON Crosstab1.Model = Crosstab2.Expr1)
INNER JOIN Crosstab3 ON Crosstab2.Expr1 = Crosstab3.Model
GROUP BY Crosstab1.Model, Crosstab1.Actual, Crosstab1.COPIED,
Crosstab1.[HIGH PRIOR PRORATE], Crosstab1.[Prorated From Actual],
NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0),
Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN, Crosstab1.CountOfModel,
Crosstab3.[<>], Crosstab3.[Accrual Cleanup], Crosstab3.EXCEL, Crosstab3.MAIL,
Crosstab3.[PBMS READINGS], Crosstab3.PRORATION, Crosstab3.UNKNOWN,
Crosstab3.VOICE, Crosstab3.WEBSITE, Crosstab3.ICR, Crosstab2.[Prorated From
Default], Crosstab2.UNKNOWN, Crosstab2.Actual, Crosstab2.COPIED,
Crosstab2.[HIGH PRIOR PRORATE], Crosstab2.[Prorated From Actual];

Post your SQL.