List client data from their first month of activity, second month, etc...

[Chris Gilpin]

Hey everybody.

I have two tables. The first table lists all of the client ids of the clients I am interested in analyzing. The second table lists various data from their different visits to the office.

I want to create a query that will list all of the clients and their visits to the office within the first month of their visit, within the second month, etc.

The tricky part (for me) is that clients start their visits at different times (for example, someone's first visit may be January 3rd, 2004 and someone else might show up for their first visit March 5th, 2005.

For example, if Client X starts his visits on March 15, 2005, I want to list all of his or her visits from March 15, 2005 to April 15, 2005. Likewise, I would like to list all of Client Y's visits from November 1, 2006 to December 1, 2006 (assuming their first visit was November 1).

Hopefully I explained the problem clearly, and that it is feasible This site is great, and thank you to everyone for your time!

-Chris

[ADezii]

Hey everybody.

I have two tables. The first table lists all of the client ids of the clients I am interested in analyzing. The second table lists various data from their different visits to the office.

I want to create a query that will list all of the clients and their visits to the office within the first month of their visit, within the second month, etc.

The tricky part (for me) is that clients start their visits at different times (for example, someone's first visit may be January 3rd, 2004 and someone else might show up for their first visit March 5th, 2005.

For example, if Client X starts his visits on March 15, 2005, I want to list all of his or her visits from March 15, 2005 to April 15, 2005. Likewise, I would like to list all of Client Y's visits from November 1, 2006 to December 1, 2006 (assuming their first visit was November 1).

Hopefully I explained the problem clearly, and that it is feasible This site is great, and thank you to everyone for your time!

-Chris

1. Create qryFirstVisit (will contain a listing of Clients and the Date of their 1st visits).
   

   SELECT tblOfficeVisits.Client, First(tblOfficeVisits.Visit) AS FirstOfVisit
   FROM tblOfficeVisits
   GROUP BY tblOfficeVisits.Client;

2. Create qryVisitsForFir stMonth. This Query will contain both qryFirstVisit and tblOfficeVisits joined on qryFirstVisit.[Client] ==> tblOfficeVisits .[Client] and will contain all visits from each Client which were less than or equal to 1 Month from their 1st visit:
   

   SELECT qryFirstVisit.Client, qryFirstVisit.FirstOfVisit, tblOfficeVisits.Visit
   FROM qryFirstVisit INNER JOIN tblOfficeVisits ON qryFirstVisit.Client=tblOfficeVisits.Client
   GROUP BY qryFirstVisit.Client, qryFirstVisit.FirstOfVisit, tblOfficeVisits.Visit
   HAVING tblOfficeVisits.Visit Between [FirstOfVisit] And DateAdd("m",1,[FirstOfVisit]);

[Chris Gilpin]

Thanks so much for your help. Your code works perfectly to get the clients visits within their first month, now I am stuck on creating queries that will list the visits in their second month, third month, etc.

I tried modifying your last code a bit:

SELECT [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
FROM [qryClinic Visit History Patient First Visit] INNER JOIN [Clinic Visit History Sorted] ON [qryClinic Visit History Patient First Visit].PatientID=[Clinic Visit History Sorted].PatientID
GROUP BY [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
HAVING [Clinic Visit History Sorted].VisitDate BETWEEN ([FirstOfVisit] + DateAdd("m",1,[FirstOfVisit])) AND DateAdd("m",2,[FirstOfVisit]);

In particular, I changed this line of your code:

HAVING [Clinic Visit History Sorted].VisitDate BETWEEN [FirstOfVisit] AND DateAdd("m",1,[FirstOfVisit]);

to

HAVING [Clinic Visit History Sorted].VisitDate BETWEEN ([FirstOfVisit] + DateAdd("m",1,[FirstOfVisit])) AND DateAdd("m",2,[FirstOfVisit]);

in an attempt to get the visits dates within the 1st to 2nd month (so if the patients first visit was on January 1st, the query would return the patients visit dates from February 1st to March 1st).

Any ideas?

EDIT: I figured it out. For those who are interested, the code looks like:

HAVING [Clinic Visit History Sorted].VisitDate BETWEEN DateAdd("m",1,[FirstOfVisit]) AND DateAdd("m",2,[FirstOfVisit]);

Now onto a more complex problem to add functionality: Is it possible to create a new query or add to this one that will check to see if there is a 'gap' in client visit dates greater than or equal to 'X' days, and if there is then their next visit after the 'X' day gap is then considered another 'first visit'?

Eg. Client 1's first visit is January 1st, 2006. He/she visits several times in January, February, and March. He or she then doesn't visit again until November 3rd. I would like to list Client 1's visit dates in the 'FirstMonthsVis its' table as the visits from January 1st to February 1st, 2006, AS WELL AS from November 3rd to December 3rd.

If I am unclear, please let me know. Thanks so much in advance!

-Chris

[ADezii]

Thanks so much for your help. Your code works perfectly to get the clients visits within their first month, now I am stuck on creating queries that will list the visits in their second month, third month, etc.

I tried modifying your last code a bit:

SELECT [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
FROM [qryClinic Visit History Patient First Visit] INNER JOIN [Clinic Visit History Sorted] ON [qryClinic Visit History Patient First Visit].PatientID=[Clinic Visit History Sorted].PatientID
GROUP BY [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
HAVING [Clinic Visit History Sorted].VisitDate BETWEEN ([FirstOfVisit] + DateAdd("m",1,[FirstOfVisit])) AND DateAdd("m",2,[FirstOfVisit]);

In particular, I changed this line of your code:

HAVING [Clinic Visit History Sorted].VisitDate BETWEEN [FirstOfVisit] AND DateAdd("m",1,[FirstOfVisit]);

to

HAVING [Clinic Visit History Sorted].VisitDate BETWEEN ([FirstOfVisit] + DateAdd("m",1,[FirstOfVisit])) AND DateAdd("m",2,[FirstOfVisit]);

in an attempt to get the visits dates within the 1st to 2nd month (so if the patients first visit was on January 1st, the query would return the patients visit dates from February 1st to March 1st).

Any ideas?

EDIT: I figured it out. For those who are interested, the code looks like:

HAVING [Clinic Visit History Sorted].VisitDate BETWEEN DateAdd("m",1,[FirstOfVisit]) AND DateAdd("m",2,[FirstOfVisit]);

Now onto a more complex problem to add functionality: Is it possible to create a new query or add to this one that will check to see if there is a 'gap' in client visit dates greater than or equal to 'X' days, and if there is then their next visit after the 'X' day gap is then considered another 'first visit'?

Eg. Client 1's first visit is January 1st, 2006. He/she visits several times in January, February, and March. He or she then doesn't visit again until November 3rd. I would like to list Client 1's visit dates in the 'FirstMonthsVis its' table as the visits from January 1st to February 1st, 2006, AS WELL AS from November 3rd to December 3rd.

If I am unclear, please let me know. Thanks so much in advance!

-Chris

You can create a [Gap] Field in tblOfficeVisits , and calculate the Gap between successive visits by Executing the following code (code only takes into account 1 Client, must factor in for > 1):

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim RS_Clone As DAO.Recordset, intGap As Integer
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblOfficeVisits", dbOpenDynaset)
 
Set RS_Clone = Me.RecordsetClone
 
MyRS.MoveFirst
RS_Clone.Move 1         'move to 2nd Record
 
Do While Not RS_Clone.EOF
  intGap = DateDiff("d", MyRS![Visit], RS_Clone![Visit])
    MyRS.MoveNext       'MyRS must be in sync with RS_Clone
      MyRS.Edit
        MyRS![Gap] = intGap
      MyRS.Update
      MyRS.MovePrevious      'move to Original position
  MyRS.MoveNext: RS_Clone.MoveNext
Loop
 
 
RS_Clone.Close
MyRS.Close
Set RS_Clone = Nothing
Set MyRS = Nothing

OUTPUT:

Client        Visit             Gap
1           1/1/2007    
1           1/28/2007       27
1           2/15/2007       18
1           3/12/2007       25
1           4/19/2007       38
1           6/2/2007       44
1           7/14/2007       42
1           8/26/2007       43
1           9/30/2007       35
1          11/23/2007       54
1          12/31/2007       38
1           1/3/2008        3

[Chris Gilpin]

I put the code listed above in VB between a Sub DateGap() and EndSub, but when I try to execute the code I get the error:

Compile Error: Invalid use of Me keyword

in reference to this line of code:

Set RS_Clone = Me.RecordsetClone

How should I execute this code to get it to run successfully?
Note: Same error applies if I try placing the code between a Function DateGap() and EndFunction

EDIT: I got it to work now, by changing the line above to:

Set RS_Clone = MyDB.OpenRecordset("tblOfficeVisits", dbOpenDynaset)

(putting this code as a Sub)

Will this change of code have any negative implications? It seems to work fine so far.
I assume the code above at the very beginning of this thread will have to be re-worked to take into account the 'gap' entry. It would have to compare the Gap size, and if it is bigger than X then it would have to count the next visit as another initial visit, but I am not sure how to code that in. Does my reasoning make sense? How would you implement that?

Thanks again,

-Chris