CrossTab Analysis for Retention

[ChuckW]

Hi,

I am trying to calculate the retention analysis per
therapist for our clinic for the last calendar year and
then make it as a report that would be on a form to be
used weekly. I have a query that lists CustomerName,
Therapist and Date and have set the date to roll for the
past year. I am specifically trying to calculate how
many people return at least one time verses total number
of patients by therapist. So in the end I want a report
that will show Therapist and a percentage. The
percentage would be the number of people who have been in
at least twice divided by the total number of patients by
Therapist.

I am going to be doing this for several clinics and
Therapists come and go. I did a cross tab which gave me
the following:

Customer Fred Barney Bambam Betty
Customer1 1 3 1
Customer2 1 2
Customer3 2 3 2
Customer4 2 2 1

So the blank values would be where Customer did not see
the therapist, a 1 shows where they saw the therapist
once etc. Is there a way to them convert what I have to:

Therapist Retention
Fred 50%
Barney 100%
Bambam 75%
Betty 33%

Thanks,

Chuck
..

 

[Michel Walsh]

Hi,


Assuming your initial table is like

Visits ' table
CustomerID, Therapist ' fields



and that Retention is the number of different customer seen, by therapist,
then



TRANSFORM COUNT(*) As theValue
SELECT COUNT(theValue) As Retention
FROM Visits
GROUP BY Therapist
PIVOT CustomerID IN(Null)



That does not return a %, but a number of different Customer, per therapist.
If you have a table Customers, without duplicated values for CustomerID in
it, then


....
SELECT COUNT(theValue) As Retention / DCOUNT("*", "Customers")
....


would return the desired percentage.



Hoping it may help,
Vanderghast, Access MVP