I am using Microsoft Access 2016 and there is table where I want to count each occurrence of a value in a column grouping by another column. Below is sample data from the table:

I want to group the data by Trip_ID and then do a rolling count for each Location. The result I am aiming for is this:

I am able to do a rolling total for the whole table by adding a temporary column equal to 1, and then using DSUM on that column.

DSUM("Temp_Col","Table", "ID<=" & ID) As RunningCount

I believe if I do some sort of subquery per Trip_ID that would do the DSUM I would get what I am looking for, but am unable to determine how to set it up.

Any help would be greatly appreciated. Thank you.

CodePudding user response：

You would need to include Trip_id and Location references in your DSum() criteria in order to get the count for each combination of those two fields. However, since you're actually after a count, use DCount() instead of "... adding a temporary column equal to 1, and then using DSUM on that column".

Here is a tested example with a DCount() expression for that derived column:

SELECT
    hp.Trip_ID,
    hp.Location,
    DCount(
        "*",
        "tblHuskypride",
        "Trip_ID='" & hp.Trip_id & "' AND Location='" & hp.Location & "' AND ID<=" & hp.ID
        ) AS RunningCount
FROM tblHuskypride AS hp
ORDER BY hp.Date;

(Notice I renamed my copy of the table to "tblHuskypride".)

Although that gives you what I think you want, I don't like all the quotes it requires. So I would prefer to use a correlated subquery as in the following example.

SELECT
    hp.Trip_ID,
    hp.Location,
    (
        SELECT Count(*)
        FROM tblHuskypride AS hp2
        WHERE
                hp2.Trip_ID=hp.Trip_ID 
            AND hp2.Location=hp.Location
            AND hp2.Date<=hp.Date
    ) AS RunningCount
FROM tblHuskypride AS hp
ORDER BY hp.Date;

Notice I chose to use the Date field instead of ID for the counting. If that's not satisfactory, swap ID for Date. Here is the output from that query tested with your sample data: