Set When Counter Starts

[MikeAuz1979]

Hi,

I'm using Access 2000 query and I have a culmlative counter that converts daily amounts to a running total. This works fine but I don't want the counter to start until a certain date. (A Contract Start date) The end result of this query is a chart that shows Culmlative consumption and ACQ between the Contract Start and End dates.

Below I have the query that works for straight forward counter but I don't want the culmlative counter to start until the Date [QryCGBConsMDQACQ.RateFrom] (The RateFrom and RateTo are the same date for every record. For example in the case of MIRN 1000 RateFrom is 01/03/2006 and RateTo is 31/05/2007)

FYI this query is actually part of an append query so please don't be concerned about the lack of proper alias

----Query Start---
SELECT QryCGBConsMDQACQ.MIRN AS Expr1, QryCGBConsMDQACQ.GasDate AS Expr2, QryCGBConsMDQACQ.DailyGJ AS Expr3, (Select Sum(DailyGJ) from QryCGBConsMDQACQ t1 where t1.GasDate <= t0.GasDate Group By MIRN) AS CulmCounter, QryCGBConsMDQACQ.CustName AS Expr4, QryCGBConsMDQACQ.MDQGJ AS Expr5, QryCGBConsMDQACQ.ContractAnnQtyGJ AS Expr6, QryCGBConsMDQACQ.RateFrom AS Expr7, QryCGBConsMDQACQ.RateTo AS Expr8
FROM QryCGBConsMDQACQ AS t0;
----Query End---

Thanks for any help

 

[MikeAuz1979]

Hi all,

I've actually gotten around this by having a prequery that is restricted to the RateFrom and RateTo dates, but if anyone has any ideas how to consolidate this into a single query then please still let me know.

Thanks in advance.
Mike

 

[PHV]

Have you tried this ?
(SELECT Sum(DailyGJ) FROM QryCGBConsMDQACQ WHERE GasDate <= t0.GasDate [!]AND (GasDate Between t0.RateFrom And t0.RateTo)[/!] GROUP BY MIRN) AS CulmCounter

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MikeAuz1979]

Thanks PHV for answering!

I did (at least something similiar to it) and it didn't start counting until the RateFrom date (Good) but when it started counting it counted from everything anyway. (Not so good) For example it produced the following kind of results.

MIRN GasDate DailyGJ CulmCounter
100 28/02/2006 10 Null
100 01/03/2006 10 20

Where 1-mar-06 is the RateFrom date.

Thanks Again