Query column return denoting records as top 80%, next 10%, & sums bottom 10%

[pacer31]

Hi all- hopefully this isn't too complicated or beyond limits..



I have a query that counts patient discharges by zip code and is in descending order. Adjacent, I've managed to create a column for the percent of total discharges with my newly learned lessons, but now need an additional column that denotes if the zip code is in the top 80% or the next 10% (80-90). Also, I need the bottom 10% to sum the discharges and be represented in the last row instead of individual zip at that point. I am a new access user and have no idea how to write this with the little experience I have. With the Return feature, it seems to only be able to return the top 55%... when I test 60% it returns all the records, because there are a lot of 1 counts for zip codes at the bottom. I suppose some sort of top-down cumulative count is needed and if expression to denote the zip codes. One particular aspect that is needed is to include the last zip code in the top 80% to be the first one crossing into the second range. (So if the cumulative fourth zip code is top 76% and the fifth adds 8%, it is still included in Top80%).

This is what I have so far...


SELECT MHDCFY09A.ZIP, MHDCFY09A.CITY, MHDCFY09A.STATE, Count(MHDCFY09A.ZIP) AS Tot, DCount("RECID","MHDCFY09A","Hospid ='2118'") AS Expr1, [Tot]/[Expr1] AS Expr2
FROM MHDCFY09A
GROUP BY MHDCFY09A.ZIP, MHDCFY09A.CITY, MHDCFY09A.STATE, DCount("RECID","MHDCFY09A","Hospid ='2118'"), MHDCFY09A.HOSPID
HAVING (((MHDCFY09A.HOSPID)="2118"))
ORDER BY Count(MHDCFY09A.ZIP) DESC;


Thanks for reading, and any help is appreciated!
Happy 4th!!!!

[Rod]

No one’s helping you, are they.

I understand what you want to do and thought I had a way to do the percentiles (correct term?). However my solution does not work when there are rows with equal counts (Count(MHDCFY09A.ZIP) which I assume is highly likely. So I abandoned the idea of doing it all in one SQL. Anyway the requirement to summarise the bottom 10% doesn’t ‘fit’ with the other requirements.

(BTW: Is DCount("RECID","MHDCFY09A","Hospid ='2118'") really necessary in the GROUP BY expression? I can’t get my head around it but it seems to me that the result will be the same for all rows so it adds nothing to the grouping.)

(There is also a minor flaw in your explanation. Suppose the first three rows account for 79% and that rows four and five have identical counts. You would include row four in the 80% and drop row five, yet it is a matter of chance which row comes first.)

I can envisage a coding solution using your SQL as a starting point. It will be quite straightforward and you will end up with a table that matches closely or exactly your reporting requirements. If you are comfortable with VBA I suggest this is the route to go.

Alternatively we are into a solution involving sub queries or intermediate queries. Such a solution may involve some make-table or append queries. I can’t quite see my way through this yet but am willing to have a go.

If you are still out there and haven’t found a solution (if you have please publish as I’m interested) then answer back with your comments and we’ll take it from there.