cumulative totals

[Tony Maddox]

Hi

I have a query, qryTurnaroundRangePathologist, which has 3 fields: CaseNo,
Path and TRange2. The first two are case accession numbers and the reporting
pathologist (both strings). The 3rd field is a string describing into which
range the number of days turnaround for the particular case falls eg 12
means between 10 and 12 days inclusive etc. Currently the ranges run from 3
to 24 in intervals of 3, the last one being 25+. This is basically the
output of the Partition function with some further string manipulations.

I want to produce cumulative percentages of cases reported, both for all
pathologists and for individuals, grouped by TRange2 for the purposes of
producing graphs. I have figured out how to do the first, using DCount, (an
adaptation of the DSum thing in the Microsoft knowledge base) as follows:

SELECT Val([TRange2]) AS TRange2Alias,
qryTurnaroundRangePathologist.TRange2,
Count(qryTurnaroundRangePathologist.CaseNo) AS Cases,
Round(DCount("[CaseNo]","[qryTurnaroundRangePathologist]","Val([TRange2])<=
" & [TRange2Alias] &
"")*100/DCount("[CaseNo]","[qryTurnaroundRangePathologist]")) AS CumPct
FROM qryTurnaroundRangePathologist
GROUP BY qryTurnaroundRangePathologist.TRange2;

which produces the count and rounded percentage for graphing purposes. My
questions are:

1) does anyone know how to do this in SQL using Count (rather than DCount)
since it should run quicker

2) does anyone know how to produce this for the individual pathologists -
I'm hoping to have a crosstab with Path as column headings (btw these can't
be fixed as the individuals vary depending on type of case, time of year
etc.)

Thanks in advance for any help.