Top counts plus % of total

[BruceS]

Objective: Based on an existing table, generate a table of the five
most common primary diagnoses given during a time period specified by
the user. (If there are two or more diagnoses with identical counts at
position five, I'd want all of these included in the table.) I'd
like three columns in the table: Dx Code, Dx Label, and % of the total
for the time period specified. As an example, suppose that tblAllDates
has 12,000 records of diagnoses; that for the time period 7/1/04
through 6/30/06, a total of 6,000 diagnoses were given; and that for
this time slice, the single most common diagnosis, with a count of 600,
was Schizophrenia Paranoid Type, which carries a code of 295.30. I'd
like the first data row in my table to read |295.30 |Schizophrenia
Paranoid Type |10% |

I can write a parameter query and use the Top Values box, but I can't
figure out how to generate the entries for that third column. Thanks
for any help.

 

[jacksonmacd]

You should be able to create a third column that shows the count of
those items ("600").

Then (and this is clunky -- perhaps somebody else can supply a better
method), you can create a fourth column that divides the count (600)
by a DCount() function that calculates the total number of diagnoses
(6000).

In fact, the third column becomes redundant -- but is useful to show
the total number of cases. When writing queries, I tend to include
these intermediate values as I build towards the solution.

HTH

 

[John Spencer]

I think the following may work for you. I've not tested the SQL statement
so I may have a syntax error.

SELECT TOP 5 DxCode, DxLabel,
Count(DxCode)/
(SELECT COUNT(*)
FROM tblAllDates
WHERE tblAllDates.SomeDateField between #7/1/04# and #6/30/05#) As
[Percentage]
FROM tblAllDates
WHERE tblAllDates.SomeDateField between #7/1/04# and #6/30/05#
GROUP BY DxCode, DxLabel
ORDER BY Count(DxCode) Desc