Profiling Members - please read.

[pilch74]

Hi.

Simply put, we have a table of order transactions, 'tblOrderTrans' -
it currently contains 6 months worth of data. This table is also
linked with two other tables, 'tblContact' and 'tblAccount'

What we want to be able to do is to do calculations on the average
times between status changes to profile members basically.

Every new member joins as Status 1. When their first payment is made
they become Status 2. Once they have 'fulfilled' their commitment they
then become Status 3.

Going back to the 'tblOrderTrans' - every single time a change happens
on a member's account it writes out a record to this table. So in
theory if I chose one member who I knew had fulfilled their commitment
I would be able to see the Membership no, status code and the date,
because this table also contains a timestamp.

I hope this makes sense and someone hear reading this would be kind
enough to assist me with this one.

Regards,

Richard.

 

[John Spencer]

Are the only statuses 1, 2, and 3?
Do members always progress from 1 to 2 to 3?
Do members never have more than one instance of each status.

If so, you have two changes from 1 to 2 and from 2 to 3.

If Status is a number field you could try

SELECT Ta.Status
, Avg(DateDiff("d",Ta.TransDate,Tb.TransDate)) as AverageTime
FROM tblOrderTrans as Ta INNER JOIN tblOrderTrans as Tb
ON Ta.MemberId = Tb.Memberid and
Ta.Status = Tb.Status -1
GROUP BY Ta.Status

IF Status is not a number field you can modify the above to
SELECT Ta.Status
, Avg(DateDiff("d",Ta.TransDate,Tb.TransDate)) as AverageTime
FROM tblOrderTrans as Ta INNER JOIN tblOrderTrans as Tb
ON Ta.MemberId = Tb.Memberid and
Val(Ta.Status) = Val(Tb.Status) -1
GROUP BY Ta.Status

or use

SELECT Ta.Status
, Avg(DateDiff("d",Ta.TransDate,Tb.TransDate)) as AverageTime
FROM tblOrderTrans as Ta INNER JOIN tblOrderTrans as Tb
ON Ta.MemberId = Tb.Memberid
WHERE Ta.Status = "1" and Tb.Status ="2" or
Ta.Status="2" and Tb.Status="3"
GROUP BY Ta.Status

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Van T. Dinh]

I am guessing you want to find the time it takes a member to move from
Status 1 to Status 2 and the same from Status 2 to Status 3 and then average
these time periods for all members?

Let's take the simple case first: that you are interested in the time, it
take each member to move from Status 2 to Status 3:

1. Firstly, you will need to find members (and date of change of Status) who
have records in [tblOrderTrans] that indicates they move to Status 3. This
should be straight-forward from your Rable if I understand your description
correctly.
2. For each member above, you will need to refer to the "previous" record of
the member that moved the member from Status 1 to Status 2 and the date of
moving. This will need a self-join from [tblOrderTrans] to the
[tblOrderTrans] above. The technique is very similar to this Usenet thread:

http://preview.tinyurl,con/2dracy

 

[pilch74]

"Do members never have more than one instance of each status."

Yes John, they do.

Every single 'change' to a member's account will result in an entry
into 'tblOrderTrans'.
Unfortunately the table doesn't just have one instance of a Status 1,2
and 3 per member.

I thought I would let you know this before trying any of your
suggestions.

Regards,

Richard

 

[John Spencer]

That makes things a lot harder for you.

So if a user has 3 records that are status 2 how would you determine the
dates to use? Do you want to use the Status 1 date for the calculation of
the change? What do you want to do if there are two or more status 1
records - use the earliest or the latest date?

Or are you just trying to get the interval between a record and its
immediate prior record? IF that is the case try

SELECT Ta.MemberID, Ta.Status,
, DateDiff("d",
(SELECT Max(Tb.TransDate)
FROM tblOrderTrans as Tb
WHERE Tb.MemberID = Ta.MemberID
AND Tb.TransDate < Ta.Transdate),
Ta.TransDate) as ElapsedDays
FROM tblOrderTrans as Ta



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[pilch74]

As far as determining the dates - for this task we're interested in
the earliest dates that a member hit a status 1, then and then 3.

From then the idea is that we would then be able to work out average

timelines per member.

Here's what the different status' mean:

Status 1 = member added onto our records - a new enrolment.
Status 2 = first payment made.
Status 3 = commitment met.

Regards,

Richard.

 

[John Spencer]

So you need a preliminary query. Saved as qFirstTime

SELECT MemberID, Status, Min(TransDate) as FirstOccurence
FROM tblOrderTrans


NOW you can use the first query structure on this query.

SELECT A.MemberID, A.Status, B.Status
DateDiff("D",A.FirstOccurence, B.FirstOccurence) as Elapsed
FROM qFirstTime as A INNER JOIN qFirstTime as B
ON A.MemberID = B.MemberID
AND A.Status = B.Status-1

If you want an overall average then
SELECT A.Status, B.Status
Avg(DateDiff("D",A.FirstOccurence, B.FirstOccurence)) as Elapsed
FROM qFirstTime as A INNER JOIN qFirstTime as B
ON A.MemberID = B.MemberID
AND A.Status = B.Status-1
GROUP BY A.Status, B.Status


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

As far as determining the dates - for this task we're interested in
the earliest dates that a member hit a status 1, then and then 3.

timelines per member.

Here's what the different status' mean:

Status 1 = member added onto our records - a new enrolment.
Status 2 = first payment made.
Status 3 = commitment met.

Regards,

Richard.

On Oct 8, 2:13 pm, "John Spencer" <[email protected]> wrote:

S N I P

 

[John Spencer]

Whoops missed a comma

So you need a preliminary query. Saved as qFirstTime

SELECT MemberID
, Status
, Min(TransDate) as FirstOccurence
FROM tblOrderTrans


NOW you can use the first query structure on this query.

SELECT A.MemberID, A.Status, B.Status
, DateDiff("D",A.FirstOccurence, B.FirstOccurence) as Elapsed
FROM qFirstTime as A INNER JOIN qFirstTime as B
ON A.MemberID = B.MemberID
AND A.Status = B.Status-1

If you want an overall average then
SELECT A.Status, B.Status
, Avg(DateDiff("D",A.FirstOccurence, B.FirstOccurence)) as Elapsed
FROM qFirstTime as A INNER JOIN qFirstTime as B
ON A.MemberID = B.MemberID
AND A.Status = B.Status-1
GROUP BY A.Status, B.Status


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[pilch74]

This is great stuff John thank you very much!

I have a few more questions to ask now that I've finally had the time
I needed to get the code working.

First here are the queries I'm using. I figured that as I'm going to
be asking another favor (or two) then it'd be useful for us both be
singing from the ame hymn sheet so to speak.

query 1 name: qFirstTime

SELECT tblOrderTrans.membno, tblOrderTrans.ordmembstatus,
Min(tblOrderTrans.orddate) AS FirstOccurence
FROM tblOrderTrans
GROUP BY tblOrderTrans.membno, tblOrderTrans.ordmembstatus;

query 2 name: qProfiled

SELECT A.ordmembstatus, B.ordmembstatus,
Avg(DateDiff("D",A.FirstOccurence, B.FirstOccurence)) as Elapsed
FROM qFirstTime as A INNER JOIN qFirstTime as B
ON A.membno = B.membno
AND A.ordmembstatus = B.ordmembstatus-1
GROUP BY A.ordmembstatus, B.ordmembstatus

John, I freaked out a little once I saw that you'd used SQL ALIASES -
very new to me, so far I'm more of a QBE man thus far although I do
occasionaly visit the SQL view. I was wondering if you'd provide a
run down what qProfiled is doing.

Would it be possible to narrow the search results down in the
qFirstTime to ONLY include members with all 3 status codes?

Regards,

Richard.

 

[John Spencer]

Explanation

The query joins two copies of qFirstTime on the membno. And then joins on
ordmembstatus by subtracting 1 form ordMembStatus in the 2nd table (so it
joins 1 to 2 and 2 to 3.

DateDiff grabs the two date instances (one from each copy of the query
qFirstTime) and calculates the difference in days and then calculates the
average of that difference.

To ensure that you have one of each status we need to modify qFirstTime. If
you know that there is always a Status 1 and Status 2 if there is a status 3
then you could use the following for aFirstTime

SELECT tblOrderTrans.membno, tblOrderTrans.ordmembstatus,
Min(tblOrderTrans.orddate) AS FirstOccurence
FROM tblOrderTrans
WHERE MembNo in (SELECT MembNo FROM tblOrderTrans WHERE ordMembStatus = 3)
GROUP BY tblOrderTrans.membno, tblOrderTrans.ordmembstatus;

IF you can't use ordMembStatus = 3 to ensure that the members have all 3
statuses, then it is time to stack more queries
QUniqueStatus
SELECT Distinct membno, ordmembstatus
FROM tblOrderTrans

qAllThree
SELECT MembNo
FROM qUniqueStatus
GROUP BY Membno
HAVING Count(OrdMembStatus) = 3

And then qFirstTime becomes

SELECT tblOrderTrans.membno, tblOrderTrans.ordmembstatus,
Min(tblOrderTrans.orddate) AS FirstOccurence
FROM tblOrderTrans
WHERE MembNo In (Select MembNo FROM qAllThree)
GROUP BY tblOrderTrans.membno, tblOrderTrans.ordmembstatus;

query 2 name: qProfiled

SELECT A.ordmembstatus, B.ordmembstatus,
Avg(DateDiff("D",A.FirstOccurence, B.FirstOccurence)) as Elapsed
FROM qFirstTime as A INNER JOIN qFirstTime as B
ON A.membno = B.membno
AND A.ordmembstatus = B.ordmembstatus-1
GROUP BY A.ordmembstatus, B.ordmembstatus


QFirstTime could be the following complicated query that may or may not run

SELECT tblOrderTrans.membno, tblOrderTrans.ordmembstatus,
Min(tblOrderTrans.orddate) AS FirstOccurence
FROM tblOrderTrans
WHERE MembNo In
( Select MembNo
FROM (
SELECT MembNo
FROM (
SELECT Distinct membno, ordmembstatus
FROM tblOrderTrans)
GROUP BY Membno
HAVING Count(OrdMembStatus) = 3))
GROUP BY tblOrderTrans.membno, tblOrderTrans.ordmembstatus;

AND in theory you could then place two copies of this query in the query
qProfiled in place of qFirstTime to make things even more complex. I think
that using the nested structure of queries would be easier for you to
understand at this point.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..