Ms Access 2010 Running Total in Query

MS Access 2010 Running Total in Query

You can get what you want with a correlated subquery.

SELECT
    a1.agentID,
    a1.incurrredDate,
    a1.points,
    a1.OneFallOff,
    a1.TwoFallOff
    (
        SELECT Sum(a2.TwoFallOff)
        FROM attendanceView AS a2
        WHERE
                a2.agentID = a1.agentID
            AND a2.incurrredDate <= a1.incurrredDate
    ) AS total
FROM attendanceView AS a1;

You could also do it with DSum, but then you need to use delimiters with agentID and incurrredDate in the DSum WhereCondition option. It seems like more effort, and I found it more error-prone, than the subquery approach.

SELECT
    a.agentID,
    a.incurrredDate,
    a.points,
    a.OneFallOff,
    a.TwoFallOff,
    DSum
        (
            "TwoFallOff", "attendanceView",
            "agentID = '" & a.agentID & "' " &
            "AND incurrredDate <= " & 
            Format(a.incurrredDate, "\#yyyy-m-d\#")
        ) AS total
FROM attendanceView AS a;

Both queries return your requested results using your sample data in Access 2007.

Running Total in Access Query

Unfortunately, MS Access does not have any of the very useful functions built into it like Oracle and others do. This is a perfect application of partitioning using RANK on those systems.

Fortunately for you, I have had to use this same kind of work around before and have a solution for you:

SELECT t1.[Location], t1.[Open Date],
    (SELECT COUNT(t2.[Location]) FROM My_Table t2 
     WHERE Format(t2.[Open Date], "yyyy-mm-dd") & "-" & t2.[Location] <= 
           Format(t1.[Open Date], "yyyy-mm-dd") & "-" & t1.[Location]) AS Running_Total
FROM My_Table t1
ORDER BY Format(t1.[Open Date], "yyyy-mm-dd") & "-" & t1.[Location];

It looks ugly, but I tested it in Access with your sample data and it works. Essentially, it runs an inline query against the same table you are selecting against and checks for how many primary keys are less than or equal to the current record. In this case, I had to derive a primary key from the date and location, but if you have another key you didn't show there is no reason that would not work too. It just has to be completely unique.

Running total within a category in Access

I can't tell about its efficiency but it works:

select t.*,
(select sum(Multiple) from tablename where Item = t.Item and [No] <= t.[No]) +
(select InventoryPos from tablename where Item = t.Item and [No] = (
  select min([No]) from tablename where Item = t.Item)
)
from tablename as t

Running Total based on dates when retroactive dates are sometimes necessary

I eventually worked it out. The solution was to create a query (Call it "Query1") based on the existing table and including a new, composite ID (named NewID) based on the date of the transaction and the existing ID (primary key). I then created a second query (Query2) based on Query1 and used this NewID as the criteria in the DSUM function. However, a few words of caution: Make sure the date is formatted as YYYYMMDD so that all dates have the same number of digits and will be sorted in chronological order. Also format the existing ID to however many digits you feel is necessary (I chose 6) when you add that to the the new date number. Lastly, you need to enclose all of it in a Val() function. Otherwise, it will show up in the query as text and may not function properly in the DSUM function.
So, here is the function in Query 2: DSUM("Amount", "tblT1", "NewID<="&NewID)

MS Access cumulative sum by date?

Im sorry but I dont have enough rep to comment.

What I think you are trying to do is a running total

SELECT (SELECT sum(revenue) from myTable a2
where a1.edate >= a2.edate)
as RunningSumField1,
edate from myTable as a1 
group by a1.edate
order by a1.edate

try this link:

MS Access 2010 Running Total in Query

---

Related Topics