I will illustrate how to use DSum() within groups of records.
Supposing the table "The Ranking" contains these records:-
Hospital --- Product Line ----- Rank - % of fy2003 total admissions
Hospital A - Product Line C --- 1 ------------ 40.00
Hospital A - Product Line C --- 2 ------------ 19.50
Hospital A - Product Line D --- 1 ------------ 10.50
Hospital A - Product Line D --- 2 ------------ 20.00
Hospital B - Product Line C --- 1 ------------ 9.50
Hospital B - Product Line C --- 2 ------------ 0.50
running this query:-
SELECT [Hospital], [Product Line], [Rank],
[% of fy2003 total admissions] as Percentage,
val(dsum("[% of fy2003 total admissions]","[The Ranking]", "[Hospital] & [Product Line] & [Rank] <= '" & [Hospital] & [Product Line] & [Rank] & "' and [Hospital] & [Product Line] = '" & [Hospital] & [Product Line] & "'")) as RunningSum
FROM [The Ranking]
ORDER BY [Hospital], [Product Line], [Rank]
will add the running sum within each group of hospital and product line:-
Hospital --- Product Line ----- Rank - Percentage -- RunningSum
Hospital A - Product Line C --- 1 ---- 40.00 -------- 40.00
Hospital A - Product Line C --- 2 ---- 19.50 -------- 59.50
Hospital A - Product Line D --- 1 ---- 10.50 -------- 10.50
Hospital A - Product Line D --- 2 ---- 20.00 -------- 30.50
Hospital B - Product Line C --- 1 ----- 9.50 --------- 9.50
Hospital B - Product Line C --- 2 ----- 0.50 -------- 10.00
As it has been pointed out by Pat, you need to have a unique identifier for each record. Here the unique identifier is the combination of Hospital, Product Line and Rank.
The comparison operators "<=" in DSum() must match with the sort order of Rank. If the query is ordered by "[Hospital], [Product Line], [Rank] DESC", then ">=" must be used.
Hope you can adapt the query to suit your need.