Running Total by product line (1 Viewer)

[Therat]

I've seen Microsoft's method to create a running total using a query, however their method creates a running total for the whole table. I have a table that ranks top physicians by hospital by product line. This is a large table because our organization owns several hospitals. The ranking is based of the percentage of the volume they bring to the hospital. The better the rank, the more volume they bring to the hospital in their service line (ex. Orthopedics). Is there a way to have a running total based on this percentage for each physician by product line?

Thanks in advance for you help.

 

[Pat Hartman]

To do a running sum in a query using the DSum() method, you need a unique identifier for each record. If you have a combination of fields that taken together will make a row unique, use a compound condition in the "where" part of the DSum().

If your table runs more than about 1,000 rows, you will find this query to be extremely slow due to the inherent inefficiency of the method used. Your best bet is to calculate the running sum in a report.

 

[Therat]

Running sum by product line

I am a novice programmer, could you help me write this compound dsum() function?

I want to create a running total for [% of fy2003 total admissions] by [product line] by [hospital]. The name of the table is "The Ranking." The table lists the [Rank] for each physician by hospital and service line. I want it to only total by product line by hospital based on the rank within each service line. In other words, I want it to start the total function with the number one ranking physician and contine till the next service line or hospital record in the table.


Thanks in advance!

 

[Rich]

You will have to do this at report level using the grouping and sorting options, no other method will work in a query with multiple grouping levels, DSum even with criteria will be unworkable

 

[Jon K]

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.

 

[Therat]

Almost there-running sum in query by pl

Jon and Pat,
Thanks for taking the time to help out. I am close to getting Pat's SQL statement to work, but I can't get it to total correctly.

Here is a small sample of my results. It correctly starts the total over after a new product line and/or hospital. However, the running sum percentage isn't correct after the number 1 ranking.

HOSP-------PLS-----------------Percentage-Ranking--RunningSum
Hospital A--Behavioral----------42.8%-----1-----------42.8%
Hospital A --Behavioral ---------35.0%-----2-----------80.0%
Hospital A --Behavioral ---------14.2%-----3------------94.3%
Hospital A --General Med --------6.9%-----1-------------6.9%
Hospital A --General Med ------- 4.5%-----2------------34.9%
Hospital A --General Med ------ 4.2%-----3------------52.1%
Hospital C--Neuroscience------20.7%-----1------------20.7%
Hospital C--Neuroscience -----13.2%----- 2-----------42.1%
Hospital C--Neuroscience -------9.9%-----3-----------52.1%
Hospital C--Neuroscience ------ 9.9%-----4-----------62.0%

I had to change my column heading's name from [% of FY2003 total admissions] to [test] because it would error out.

Here is my SQL statement:
SELECT [The Ranking].HOSP, [The Ranking].PLS, [The Ranking].Test AS Percentage, [The Ranking].Ranking, Val(DSum("[test]","[The Ranking]","[Hosp] & [Pls] & [Ranking] <= '" & [Hosp] & [Pls] & [Ranking] & "' and [Hosp] & [Pls] = '" & [Hosp] & [Pls] & "'")) AS RunningSum
FROM [The Ranking];

Any thoughts? Thanks again!

 

[Jon K]

When I copied your code and ran it on your data, I got this result.

HOSP ------- PLS ------ Percentage - Ranking - RunningSum
Hospital A - Behavioral ------ 42.8 ---- 1 ------ 42.7999992370605
Hospital A - Behavioral -------- 35 ---- 2 ------ 77.7999992370605
Hospital A - Behavioral ------ 14.2 ---- 3 ------ 91.9999990463257
Hospital A - General Med ----- 6.9 ---- 1 ------ 6.90000009536743
Hospital A - General Med ----- 4.5 ---- 2 ------ 11.4000000953674
Hospital A - General Med ----- 4.2 ---- 3 ------ 15.5999999046326
Hospital C - Neuroscience --- 20.7 ---- 1 ------ 20.7000007629395
Hospital C - Neuroscience --- 13.2 ---- 2 ------ 33.9000005722046
Hospital C - Neuroscience ---- 9.9 ---- 3 ------ 43.8000001907349
Hospital C - Neuroscience ---- 9.9 ---- 4 ------ 53.6999998092651


Instead of showing one decimal place as with my original data, the RunningSums are now in 16 digits. However, they are correct.

I got the same result when I ran the database in Access 2000.

I have no idea why the RunningSum doesn't work correctly for you. Have you tried it on another computer?

I attach my database here (RunningSum_Access97.zip 11K). Perhaps you can try it and see if you get the same result as mine.

--------------------
If you want to show the RunningSums as 42.8%, 77.8%, etc, you can use the format() function instead of the val() function, (but then they will be text strings and left aligned):-

format(DSum("[test]","[The Ranking]","[Hosp] & [Pls] & [Ranking] <= '" & [Hosp] & [Pls] & [Ranking] & "' and [Hosp] & [Pls] = '" & [Hosp] & [Pls] & "'"),"#.0\%") AS RunningSum

----------------------------
Edit:
The code to produce the running sum can be improved, making it simpler and easier to comprehend. A new RunningSum_Access97_improved.zip 11K is attached.