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!!!!