Running Subtotals in Microsoft Access SQL

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: