Help needed to finish a query that display results of a subquerybased upon a count...

[madforplaid]

Hi,

Given this image, http://members.dslextreme.com/users/madforplaid/queryCount.jpg,
and its SQL as follows:

SELECT Q.FullKey, Q.TimeBucket, COUNT(Q.FullKey) AS Count,
SUM(Q.Quantity) AS Quantity, Q.TtlCust, 'U0RZ' & MID AS LOCMID, 'U0RZ'
AS LOC, Q.MID FROM (SELECT Left(tblAdvertising_Cp.FullKey,72) & 'U0RZ'
& Right(tblAdvertising_Cp.FullKey,5) AS FullKey,
tblAdvertising_Cp.TimeBucket, Sum(tblAdvertising_Cp.Quantity) AS
Quantity, tblAdvertising_Cp.TtlCust, tblAdvertising_Cp.MID FROM
(tblAdvertising_Cp INNER JOIN tblCustomerTimePhase ON
tblAdvertising_Cp.TtlCust=tblCustomerTimePhase.TtlCust) INNER JOIN
tblApprovedDistributionList ON
tblAdvertising_Cp.MID=tblApprovedDistributionList.MID WHERE
(((tblAdvertising_Cp.TimeBucket)<tblCustomerTimePhase.TimeBucket) And
((tblAdvertising_Cp.LOC)='U0BZ' Or (tblAdvertising_Cp.LOC)='U0RZ') And
((tblAdvertising_Cp.TtlCust)=tblCustomerTimePhase.TtlCust) And
((tblAdvertising_Cp.MID)=tblApprovedDistributionList.MID)) GROUP BY
FullKey, tblAdvertising_Cp.TimeBucket, tblAdvertising_Cp.TtlCust,
tblAdvertising_Cp.MID) AS Q GROUP BY Q.FullKey, Q.TimeBucket,
Q.TtlCust, Q.MID;

I need to return the results with a Quantity of 0 for Counts > 1 and
the actual Quantity for Counts = 1 while also removing the Count field
from the results.

Any help is greatly appreciated.

Thanks,

Paul

 

[madforplaid]

For those interested, I have resolved it with this SQL wrapped around
the subquery:

SELECT F.FullKey, F.TimeBucket, IIF(F.Count>1,0,F.Quantity) AS
Quantity, F.TtlCust, F.LOCMID, F.LOC, F.MID FROM (SELECT Q.FullKey,
Q.TimeBucket, COUNT(Q.FullKey) AS Count, SUM(Q.Quantity) AS Quantity,
Q.TtlCust, 'U0RZ' & MID AS LOCMID, 'U0RZ' AS LOC, Q.MID FROM (SELECT
Left(tblAdvertising_Cp.FullKey,72) & 'U0RZ' &
Right(tblAdvertising_Cp.FullKey,5) AS FullKey,
tblAdvertising_Cp.TimeBucket, Sum(tblAdvertising_Cp.Quantity) AS
Quantity, tblAdvertising_Cp.TtlCust, tblAdvertising_Cp.MID FROM
(tblAdvertising_Cp INNER JOIN tblCustomerTimePhase ON
tblAdvertising_Cp.TtlCust=tblCustomerTimePhase.TtlCust) INNER JOIN
tblApprovedDistributionList ON
tblAdvertising_Cp.MID=tblApprovedDistributionList.MID WHERE
(((tblAdvertising_Cp.TimeBucket)<tblCustomerTimePhase.TimeBucket) And
((tblAdvertising_Cp.LOC)='U0BZ' Or (tblAdvertising_Cp.LOC)='U0RZ') And
((tblAdvertising_Cp.TtlCust)=tblCustomerTimePhase.TtlCust) And
((tblAdvertising_Cp.MID)=tblApprovedDistributionList.MID)) GROUP BY
FullKey, tblAdvertising_Cp.TimeBucket, tblAdvertising_Cp.TtlCust,
tblAdvertising_Cp.MID) AS Q GROUP BY Q.FullKey, Q.TimeBucket,
Q.TtlCust, Q.MID) AS F;