Difficult Query Problem - Probably 1

[pdldavis]

Hi, I have a query with three fields - Qty, CCID, and UseLast(calculated).

If possible, I would like the calculated field (UseLast) to use the last CCID where qty is not 0 and use the matching CCID where there is a quantity:

Qty CCID UseLast
11 1 1
22 2 2
34 3 3
0 4 3
0 5 3
26 6 6
0 7 6
23 8 8

SELECT tblProb.Qty, tblProb.CCAlias, IIf([qty]=0,"Need Help - use previous CcId no",[CcId]) AS UseLast
FROM mtActRate AS tblProb
GROUP BY tblProb.Qty, tblProb.CcId, IIf([qty]=0,"Need Help - use previous CcIdno",[CcId])
ORDER BY tblProb.CcId;

Any help would be appreciated. Thanks

 

[rac2]

Maybe picture two tables, one for the ccid that have a qty and those that do not.

The haves

SELECT Qty,
       CcId,
       CcId AS UseLast
FROM mtActRate
WHERE Qty > 0

and the have-nots

SELECT a.Qty,
       a.CcId,
       ( SELECT MAX(CcId) FROM mtActRate
         WHERE Qty > 0
           AND CcId < a.CcId
        ) AS UseLast
FROM mtActRate a
WHERE a.Qty = 0

Combine the results of the two queries with UNION

SELECT Qty,
       CcId,
       CcId AS UseLast
FROM mtActRate
WHERE Qty > 0

UNION

SELECT a.Qty,
       a.CcId,
       ( SELECT MAX(CcId) FROM mtActRate
         WHERE Qty > 0
           AND CcId < a.CcId
        ) AS UseLast
FROM mtActRate a
WHERE a.Qty = 0

ORDER BY CcId

The have-nots query uses a correlated subquery, meaning that it takes a value from the outer query, here the value from the outer query is a.CcId.

You might be able to use these ideas with the IIf function as well.


I am curious; what are these things actually?

 

[pdldavis]

Hi Rac2 thank you. That will do the trick. The purpose of this is to obtain a cumulative average for a chart.

The CcID was an autonumber used to obtain a running sum using an approach similar to what you did above.

I then needed to use that autonumber to divide the running sum by to obtain a cumulative average, except where the quantity was zero.

This will work.

Thanks for your time.