Running total between two dates

  • Thread starter Thread starter Noreen
  • Start date Start date
N

Noreen

I have a query in which I need to calculate running totals on a no. of
fields, mainly Year To Date figures. The problem is my year includes the
31/12/07. My existing code is below, I need to modify it to include the last
day of last year. Any ideas??


INSERT INTO Blank2 ( [Date], StoreId, RunTotLYRT, Company )
SELECT QueryCOMB.Date, QueryCOMB.StoreId, (SELECT SUM([LY Read Total])
FROM QueryCOMB Q2
WHERE Q2.[Date] <= QueryCOMB.[Date]
AND Year(Q2.[Date]) = Year(QueryCOMB.[Date])
AND Q2.StoreID = QueryCOMB.StoreID) AS RunTotLYRT, QueryCOMB.Company
FROM QueryCOMB
WHERE (((QueryCOMB.Date)=[Enter week end Date]) AND
((QueryCOMB.StoreId)="eyre square"))
ORDER BY QueryCOMB.Date;
 
( Q2.Date BETWEEN DateSerial( year(queryComb.[date]), 1, 0) AND
DateSerial( year(queryComb[date] + 1, 1, 0) )

instead of

Year(Q2.[Date]) = Year(QueryCOMB.[Date])

Note that the 0th day of January Year XYZ is the same as one day before
January First, year XZY, since 0 = 1 (First) minus 1 (day) ...
obviously... or maybe not so obvious after all :-)

Remember that with between, the two limits are inclusive. On the other hand,
if QueryComb.[date] owns a time, in addition to a date, may have to use:


( Q2.Date >= DateSerial( year(queryComb.[date]), 1, 0)
AND Q2.Date < DateSerial( year(queryComb[date] + 1, 1, 1) )


with a strict inequality on the modified upper limit.



Vanderghast, Access MVP
 
Back
Top