Profiling Members - please read.

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.

0
pilch74
10/8/2007 10:40:38 AM
access.queries 6343 articles. 1 followers. Follow

9 Replies
585 Views

Similar Articles

[PageSpeed] 28

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
..

<pilch74@gmail.com> wrote in message 
news:1191840038.078231.277270@57g2000hsv.googlegroups.com...
> 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.
> 


0
John
10/8/2007 11:35:07 AM
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

-- 
HTH
Van T. Dinh
MVP (Access)




<pilch74@gmail.com> wrote in message 
news:1191840038.078231.277270@57g2000hsv.googlegroups.com...
> 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.
> 


0
Van
10/8/2007 11:50:06 AM
"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

On Oct 8, 12:35 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> 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
> .
>
> <pilc...@gmail.com> wrote in message
>
> news:1191840038.078231.277270@57g2000hsv.googlegroups.com...
>
> > 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.


0
pilch74
10/8/2007 12:43:41 PM
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@gmail.com> wrote in message 
news:1191847421.021114.212670@r29g2000hsg.googlegroups.com...
> "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
>
> On Oct 8, 12:35 pm, "John Spencer" <spen...@chpdm.edu> wrote:
>> 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
>> .
>>
>> <pilc...@gmail.com> wrote in message
>>
>> news:1191840038.078231.277270@57g2000hsv.googlegroups.com...
>>
>> > 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.
>
> 


0
John
10/8/2007 1:13:24 PM
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.

On Oct 8, 2:13 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> 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
> .
>
> <pilc...@gmail.com> wrote in message
>
> news:1191847421.021114.212670@r29g2000hsg.googlegroups.com...
>
> > "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
>
> > On Oct 8, 12:35 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> >> 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
> >> .
>
> >> <pilc...@gmail.com> wrote in message
>
> >>news:1191840038.078231.277270@57g2000hsv.googlegroups.com...
>
> >> > 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.


0
pilch74
10/8/2007 2:15:16 PM
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@gmail.com> wrote in message 
news:1191852916.112890.155620@o3g2000hsb.googlegroups.com...
> 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.
>
> On Oct 8, 2:13 pm, "John Spencer" <spen...@chpdm.edu> wrote:

 S N I P

>> >> > 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.
>
> 


0
John
10/8/2007 3:48:06 PM
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
..

"John Spencer" <spencer@chpdm.edu> wrote in message 
news:OrCmMKcCIHA.4308@TK2MSFTNGP06.phx.gbl...


> <pilch74@gmail.com> wrote in message 
> news:1191852916.112890.155620@o3g2000hsb.googlegroups.com...
>> 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.
>>
>> On Oct 8, 2:13 pm, "John Spencer" <spen...@chpdm.edu> wrote:
>
> S N I P
>
>>> >> > 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.
>>
>>
>
> 


0
John
10/8/2007 3:51:58 PM
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.

On Oct 8, 4:51 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> 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
> .
>
> "John Spencer" <spen...@chpdm.edu> wrote in message
>
> news:OrCmMKcCIHA.4308@TK2MSFTNGP06.phx.gbl...
>
> > <pilc...@gmail.com> wrote in message
> >news:1191852916.112890.155620@o3g2000hsb.googlegroups.com...
> >> 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.
>
> >> On Oct 8, 2:13 pm, "John Spencer" <spen...@chpdm.edu> wrote:
>
> > S N I P
>
> >>> >> > 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.


0
pilch74
10/9/2007 1:00:21 PM
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
..

<pilch74@gmail.com> wrote in message 
news:1191934821.310741.26200@o3g2000hsb.googlegroups.com...
> 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.
>
> On Oct 8, 4:51 pm, "John Spencer" <spen...@chpdm.edu> wrote:
>> 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
>> .
>>
>> "John Spencer" <spen...@chpdm.edu> wrote in message
>>
>> news:OrCmMKcCIHA.4308@TK2MSFTNGP06.phx.gbl...
>>
>> > <pilc...@gmail.com> wrote in message
>> >news:1191852916.112890.155620@o3g2000hsb.googlegroups.com...
>> >> 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.
>>
>> >> On Oct 8, 2:13 pm, "John Spencer" <spen...@chpdm.edu> wrote:
>>
>> > S N I P
>>
>> >>> >> > 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.
>
> 


0
John
10/9/2007 3:57:29 PM
Reply:

Similar Artilces:

Help Please revert file
I am running Excel 2000. I opened a file up and did modifications. I then wanted to save as another file unfortunately I hit the save button. Is there any way I can get my old file back. Ron Hi Ron, The unfortunate answer is that unless you have a backup copy or suitable recovery software installed, you will not be able to undo the changes. -- Regards, Norman "Ron Gibson" <aspexpert@comcast.net> wrote in message news:%23x0nLYW$EHA.3416@TK2MSFTNGP09.phx.gbl... >I am running Excel 2000. I opened a file up and did modifications. I then > wanted to save as an...

Please help me...its VERY Urgent
Hi to all I have a problem taht is like... I make a customize MS agent character with the help of MS character agent editor. Now i want to integrate this agent with my VC++ application but i am not getting how can i code to integrate it or how VC++ take a .acs files to display that animation or agent? its very urgent plz help me as soon as possible. "Adroit" <sweet.sapna9@gmail.com> wrote in message news:94f95c49-4db4-47a3-b0b2-0d87284ff066@j28g2000hsj.googlegroups.com... > Hi to all > > I have a problem taht is like... > ...

Sorting numbers, please help?
I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1 to 100. I need to count how many times each number appears in each column or even in all 5 columns together. Ex. the number 1 appears 252 times, etc. Does anyone know how to sort the data that way? I have imported into an access table as well if that's easier. Please help? you could use CountIf function on excel. e.g CountIF("A1:A500", 1) which will should how many time 1 appear from range (A1 to A500). "Kevin Schultz" wrote: > I ahve a spreadsheet that contains 5 columns of w...

Switch Profile in OL 2002
Suddenly I am haivng trokuble switching profiles in OL 2002 (Win XP). I have two profiles, one for business and the other personal. As I recall this was to keep business e-mail and personal e-mail separate. (Maybe that was not a great idea but that was the advice I was given a year or so ago) Until yesterday I was able to switch OL rofiles simply by closing OL and restarting it. When it restarted it would prompt me to select a profile. Now when I restart OL it no longer prompts me for a profile but uses the same profile as when it was last open. I can't figure out how to...

read message
Hi to all How can I disable the option that reply automatic for read message ? thanx Tools->options->email options->tracking options. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Essam asked: | Hi to all | | How can I disable the option that reply automatic for read message ? | | thanx ...

Unable to load Plug-in assembly
Hi, while trying to ‘Save’ (or ‘Save and Close’) a contact after changing their last name I get an error message "Unable to load Plug-in assembly". Can anyone help me on this. This is urgent please. thanks Karthik Do you have a Plug-In? Was this an upgrade from 3.0 to 4.0? -- Fortis www.webfortis.com "Karthik-" wrote: > Hi, > > while trying to ‘Save’ (or ‘Save and Close’) a contact after changing their > last name I get an error message > > "Unable to load Plug-in assembly". > > Can anyone help me on this. This is urgent ple...

Help pLease
Hi all, Any chance to change the one of the field value (appro 500 records) in an expired contract. the chaging value is from the same contract form field. Advance Thank you for your support Sujith Well, this is not a supported way of doing it. you can probably go to the MSCRM database to change the value for that field. This field should be in the ContractBase or ContractExtensionBase table. Warning: do it on your own risk. before you do it make sure you backup your CRM databases just incase. Darren Liu Crowe Chizek and Company http://www.crowecrm.com On 3=D4=C222=C8=D5, =CF=C2=CE=E71=...

Cannot read Notes contacts by using Outlook
Hi, all, I have the following installed: MS Outlook 2003 MS Outlook Connector for Domino (Outlook 2003/2002 Add-in: Notes Connector: http://www.microsoft.com/downloads/details.aspx?FamilyID=8ebbba59-5f17-4e52-8980-c4f0dfa92d65&DisplayLang=en) Domino Notes Client 5.0.11 With Outlook Connector, I can smoothly sync between my Outlook and Notes, except that my Outlook cannot sync the Personal Address Book of my Notes. My Outlook can view all the server address book of Notes but cannot view the local Personal Address Book of Notes. What should I do? Many thanks. ...

Current Views........Help Please!!!!!
I have one guy that keeps loosing columns in his inbox (ie. subject\received columns). I know he isn't going through the proper way of changing views and editing these features. (ie right clicking and customizing current views). Is anyone aware of any key strokes or something else that he might be doing to removed these columns. HELP PLEASE! ...

help with Disabled code please
Hello All ! Was just wondering if someone can help me with a little code. I know bits of what I want to do but getting a bit confused... Basically I have two forms, on one form is a button that opens up the second form using... On_Click DoCmd.OpenForm "Jobs", , , , acFormAdd what I would also like it to do is from that button only that when the second form opens certain buttons and combos are disabled. I want this to only happen from the button and not if the 2nd form is opened on its own without the 1st form. Im guessing that I need to add additional code to the on click part ...

modeless CDialog access to CListView members
Hi I have created a modeless CDialog void CDPNotifierView::OnFilteron() { if(m_pFilterDlg=3D=3D NULL) { m_pFilterDlg=3D new CFilterDlg(); m_pFilterDlg->SetData(GetDocum=ADent()->m_arrEventLevels, GetDocument()->m_arrDevices); m_pFilterDlg->Create(CFilterDl=ADg::IDD, this); } m_pFilterDlg->ShowWindow(SW_SH=ADOW); } I have an apply button in this CFilterDlg, when the user click on the apply button a filter have to be set in the CListView. How can I acces to this CListView members. Or is it possible to be informed in the CListView class when the apply button in ...

BSOD in one profile only...
Hey folks, I'm running Windows 7 Pro, and my laptop has two user accounts, work and home. Both are administrator accounts and whenever I install any software if is always installed "for everyone". So far so good, I'm really impressed with W7. However, I will sometimes get a BSOD in the *work* profile. It never happens in the home profile. Q. How do I go about troubleshooting what could be causing it since it seems to me that a BSOD is usally caused by something more "general"... but if so, why does it only happen in the work profile... and randomly...

Index, Match, Lookup HELP PLEASE!
First I want to say I have read many posts and tried many suggestions on those post before posting my question hoping I wouldn't have to ask a question that has been answered several times already. I am creating an Excel workbook for my business math class. On one page is the actual math problems and on the other two sheets, each one has a table. One table is Present value of $1 and the other is Future Value of a $1. When I try and put a formula in the math problem sheet that is referenced to one of the tables I get #NA or one of the many other error messages. Here is ...

Multiple Profiles in Outlook 2002
I have multiple profiles set up in Outlook: one for me, one for my wife, one I use for graduate school, etc. When Outlook starts, I'm prompted for the profile to use. If I have not rebooted the system since the last time I started Outlook, and I use a different profile from the last time, I get the following error message: Microsoft Visual C++ Runtime Library Runtime Error! Program: <path to Outlook.exe> abnormal program termination If I click on OK, and try again, it works fine from there on out ... until I use a different profile without rebooting first. I chec...

how to recover my personal folder .pst file with in user profile
Actually, i create a personalfolder file x.pst in to one user profile. after that for getting the same screen settings of the other user. i copy that user profile into mine after that i can't open outlook personal folders. I using search for that file, even i cant get it at anywhere pls help urgent If you're using Windows 2000 or XP, make sure you're searching in hidden and system folders to find all existing .PST files. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the messag...

please help 09-18-06
1. I have a computer that uses mcirosoft Access 2003. 2. I have access to my database files. 3. I can type in information on a form. 4. I know how to create queries to store data in. My question is this Can somebody please help me understand how to make my v-chip for my t.v. work with the access database. I also know how to create hyperlinks, if that helps. HTH Lisa Hi Lisa, What you lack, it seems, is information about controlling the v-chip from a Windows computer. There's no built-in facility in Access for this, and it will take some sort of additional software to "...

Reading my home email while I'm at work
I have Outlook Express 6 on my computer at home and at work. Is there a way I can check my home email from my office computer? Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomsterdam.com --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, a...

Please help, windows mail getting dialup box?
I have Qwest DSL service, but when tying to open or send Email via windows mail, a blank dialup box appears and I cannot send or receive any windows mail. The connection settings look to be set right, never dial a connection and set for LAN, automatic and proxy server. The proxy server address is "proxy.integrity.com. "and port 80. Is this right? Until recently I did had ISP Integrity Online and dialup service. Also the mail server is set for "POP3 default" Any ideas on what I should do to to correct this please let me know? I have already been round &...

Won't read or see install disc? #2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I am trying to install 2008 and the disc will not appear on desktop or in Finder. The disc loads fine on other Macs here at home and this Mac is reading other CDs & DVDs fine. Any ideas why it simply won't see it? On 10/24/09 11:57 PM, in article 59b7f4a1.-1@webcrossing.caR9absDaxw, "Blungld@officeformac.com" <Blungld@officeformac.com> wrote: > I am trying to install 2008 and the disc will not appear on desktop or in > Finder. The disc loads fine on other Macs here at home and this Mac ...

Reading shared inbox email without it being marked read
Maybe this sounds strange but apparently Lotus Notes works this way. A user has permissions to her bosses email so they can both read it. But when she reads his email from her PC, she does not want it to be marked as read on his PC (so her boss knows he hasn't read it). - She doesn't want to keep changing them to unread from read. Someone thinks it should not come up as read when she opens its. I'm not aware of that being possible. Is it? Preview pane - too much trouble for her. Outlook 2003/Exchange 2003 Thanks Paul Paul Hancock <paul-hancock@dlfi.com> wrote: &...

read reciepts
I have an interesting question; is there a way to sort or have a report from all "sent mail" with "read receipt request" that I did not get a read proof? So I can follow up which mail was not read yet? Since many, if not most, users don't send read receipts, it would be rather difficult. "JACOB" <jacob.montreal@gmail.com> wrote in message news:OdgS6DL%23IHA.1228@TK2MSFTNGP02.phx.gbl... :I have an interesting question; is there a way to sort or have a report from : all "sent mail" with "read receipt request" that I did not get a...

read if you're having passport problems
Hi- I was just having problems signing into money w/ my passport. What follows is what the MS support person suggested I do, and it managed to fix the problem that had been bothering me for 2 weeks: ----- Dear Dan, I apologize for the mistake I have made to write 2004 instead of 2002. Focusing on this issue, I am sorry to hear that the problem still persists. Actually, we have received several service requests which detail the same Passport sign in issue in Money. We have reported this problem to our product team. Based on their feedback, the problem is that one of a Passport se...

Is there any way to stop messages being incorrectly marked as "replied" please?
Hello, If you save a draft reply to a message, Outlook marks it as "replied" even if you've never sent the message because you cancelled it or your mailbox was full. Is there any way we can prevent that? This seems to extend right to Outlook 2007. BTW if a mailbox is over-quota, can I still save a draft?! If not, why is it still marked as "replied"! We're just migrating to Exchange and it's driving our users crazy. Thanks, - Alan If Outlook saves a message as a draft, it adds the replied to flag. Otherwise, there is no easy way for outlook...

READING SAVED BAND E MAIL?
I am trying to figure out how I can re read E Mail sent to me by my bank that was already read and saved in Money 2003? Thanks, Arty On Fri, 09 Jan 2004 20:09:54 GMT, ART SANTELLA <santella@optonline.net> wrote: >I am trying to figure out how I can re read E Mail sent to me by my bank that was >already read and saved in Money 2003? >Thanks, > >Arty Thanks, Arty ...

Prompt to send "Read Receipt"
Is there any way to prompt me to send a read receipt on outgoing emails? I want something to pop up reminding me to attach one. I don't always want to send one, but tend to forget if I don't make it automatic. Tools, Options, Preferences, E-mail Options, Tracking Options On the otherhand many people set their system to flush such requests. bec wrote: > Is there any way to prompt me to send a read receipt on outgoing emails? I > want something to pop up reminding me to attach one. I don't always want to > send one, but tend to forget if I don't make...