stack query from a group query

[inungh]

I have a following query to calculate base on previous query named [SP
RPT GET RRE CSI PRE]

The pre query some times does not have SumA or SumB which get errors
on this query, since there is no record for SumA and SumB.

Are there any workaround for this?

Your help is great appreciated,



SELECT EDA.EmployeeNO, [SP RPT GET RRE CSI PRE].SplitID, Round(Sum((IIf
(IsNull([SumA]),0,[SumA])+IIf(IsNull([SumB]),0,[SumB]))/(IIf(IsNull
([SumA]),0,[SumA])+IIf(IsNull([SumB]),0,[SumB])+IIf(IsNull([SumC]),0,
[SumC])+IIf(IsNull([SumD]),0,[SumD])))*100,2) AS CSI, Sum([SP RPT GET
RRE CSI PRE].SACD) AS ACD
FROM [SP RPT GET RRE CSI PRE] INNER JOIN (tblSplits INNER JOIN
(tblPIPEmployee AS Supervisors INNER JOIN (tblPIPEmployee AS EDA INNER
JOIN tblPIPEmployee ON EDA.Employee_ID = tblPIPEmployee.EDA) ON
Supervisors.Employee_ID = tblPIPEmployee.Supervisors) ON
(tblSplits.SplitID = tblPIPEmployee.GroupID) AND (tblSplits.DeptID =
tblPIPEmployee.DeptID)) ON [SP RPT GET RRE CSI PRE].EmployeeNO =
tblPIPEmployee.EmployeeNO
GROUP BY EDA.EmployeeNO, [SP RPT GET RRE CSI PRE].SplitID;

 

[KARL DEWEY]

Have you tried to put the Nz function in the pre-query so that a record with
zero is produced?

 

[MGFoster]

I have a following query to calculate base on previous query named [SP
RPT GET RRE CSI PRE]

The pre query some times does not have SumA or SumB which get errors
on this query, since there is no record for SumA and SumB.

Are there any workaround for this?

Your help is great appreciated,



SELECT EDA.EmployeeNO, [SP RPT GET RRE CSI PRE].SplitID, Round(Sum((IIf
(IsNull([SumA]),0,[SumA])+IIf(IsNull([SumB]),0,[SumB]))/(IIf(IsNull
([SumA]),0,[SumA])+IIf(IsNull([SumB]),0,[SumB])+IIf(IsNull([SumC]),0,
[SumC])+IIf(IsNull([SumD]),0,[SumD])))*100,2) AS CSI, Sum([SP RPT GET
RRE CSI PRE].SACD) AS ACD
FROM [SP RPT GET RRE CSI PRE] INNER JOIN (tblSplits INNER JOIN
(tblPIPEmployee AS Supervisors INNER JOIN (tblPIPEmployee AS EDA INNER
JOIN tblPIPEmployee ON EDA.Employee_ID = tblPIPEmployee.EDA) ON
Supervisors.Employee_ID = tblPIPEmployee.Supervisors) ON
(tblSplits.SplitID = tblPIPEmployee.GroupID) AND (tblSplits.DeptID =
tblPIPEmployee.DeptID)) ON [SP RPT GET RRE CSI PRE].EmployeeNO =
tblPIPEmployee.EmployeeNO
GROUP BY EDA.EmployeeNO, [SP RPT GET RRE CSI PRE].SplitID;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're probably getting "divide by zero" errors. I don't know, you
didn't tell us the error. Simplify your query by using Nz() instead of
IIf(). Add a criteria that disqualifies rows (records) from the first
query that have NULL values in the SumA, SumB, etc. columns.

SELECT EDA.EmployeeNO,
C.SplitID,
Round(SUM(Nz(SumA,0) + Nz(SumB,0)) / Nz(SumA,0) + Nz(SumB,0) +

Nz(SumC,0) + Nz(SumD,0)) * 100, 2) As CSI
Sum(C.SACD) AS ACD

FROM [SP RPT GET RRE CSI PRE] As C
INNER JOIN (tblSplits As S
INNER JOIN (tblPIPEmployee AS Supervisors
INNER JOIN (tblPIPEmployee AS EDA
INNER JOIN tblPIPEmployee AS PE
ON EDA.Employee_ID = PE.EDA)
ON Supervisors.Employee_ID = PE.Supervisors)
ON (S.SplitID = PE.GroupID)
AND (S.DeptID = PE.DeptID))
ON C.EmployeeNO = PE.EmployeeNO

WHERE Nz(SumA,0) + Nz(SumB,0) + Nz(SumC,0) + Nz(SumD,0) > 0

GROUP BY EDA.EmployeeNO,
C.SplitID;

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmjI54echKqOuFEgEQLA+ACbBjtPUsE9byc1mPD9UZWK2FMqckEAoJ8k
o3TIL7TDm9+CNbi/OVhj/8+J
=8VUY
-----END PGP SIGNATURE-----