Cross Tab 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

 

[PC Datasheet]

Looks like everyone loves BamBam!

 

[ChuckW]

Please do not reply to any questions people post unless
you have something of value to say.

 

[PC Datasheet]

Touchy!!!

How much value did your post here have??

 

[Tom Ellison]

Dear Chuck:

Assuming we can work with 2 columns in a properly designed junction
table, those being Customer and Therapist, this may do it:

SELECT Therapist,
(SELECT 100. * COUNT(*) FROM (SELECT Customer FROM Junction T1
WHERE T1.Therapist = T.Therapist GROUP BY Customer
HAVING COUNT(*) > 1) x )
/
(SELECT COUNT(*) FROM (SELECT Customer FROM Junction T2
WHERE T2.Therapist = T.Therapist GROUP BY Customer) x )
AS Retention
FROM Junction T
GROUP BY Therapist

Please correct the column names and the name of your table before
using this.

I have tested this against both Jet and MSDE. MSDE gives the results
you want. Jet doesn't handle this, due to a bug that is long known.
In many cases, Jet won't let you span two levels of subquery, as this
does. So, it prompts you for T.Therapist.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Trying to mediate and be fair.

PCD does often post useful responses here. He deserves some respect.

However, many of us look at a thread and see it has been answered,
then proceed to ignore it. In my case, this happens all the more so
when someone competent like PCD has responded. Fortunately, it
doesn't seem to have happened this time (I was already working on a
response at the time) but it could have cost Chuck an answer he may
really need.

My own policy is to refrain from interesting comments until after the
post has been answered. That way, it won't have a negative effect on
someone who really needs an answer.

So, because I respect PCD's ability to give good answers here, I might
have ignored the original question. That's the possible problem here.

It is my wish now only to mediate and try to smooth feelings here. I
have made a response to Chuck's question that took a while to prepare.
I have also had contact with PCD before, and I believe we have always
shown respect for each other. So, if I have any capital with you
both, let's please just learn from this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Chuck:

If you must make this work for Jet, then the two levels of subquery
must be altered to work as separate saved queries. Let me know and
I'll help with that if needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Chuck:

Very sorry if we've scared you away.

Is there any chance you are interested in a solution to your problem?
I'm very interested in hearing from you, as I worked quite a while to
obtain a working solution for this, even if it works only in a limited
environment. It can be at least the start of solution we can make
work in your situation.

Because I spent a fair amount of time on this, would you be so kind as
to respond?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts