By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

P: 23
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
Sep 12 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,616
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).
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblOfficeVisits.Client, First(tblOfficeVisits.Visit) AS FirstOfVisit
    2. FROM tblOfficeVisits
    3. GROUP BY tblOfficeVisits.Client;
  2. Create qryVisitsForFirstMonth. 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:
    Expand|Select|Wrap|Line Numbers
    1. SELECT qryFirstVisit.Client, qryFirstVisit.FirstOfVisit, tblOfficeVisits.Visit
    2. FROM qryFirstVisit INNER JOIN tblOfficeVisits ON qryFirstVisit.Client=tblOfficeVisits.Client
    3. GROUP BY qryFirstVisit.Client, qryFirstVisit.FirstOfVisit, tblOfficeVisits.Visit
    4. HAVING tblOfficeVisits.Visit Between [FirstOfVisit] And DateAdd("m",1,[FirstOfVisit]);
Sep 12 '07 #2

P: 23
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
  2. 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
  3. GROUP BY [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
  4. 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:

Expand|Select|Wrap|Line Numbers
  1. HAVING [Clinic Visit History Sorted].VisitDate BETWEEN [FirstOfVisit] AND DateAdd("m",1,[FirstOfVisit]);
to

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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 'FirstMonthsVisits' 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
Sep 18 '07 #3

ADezii
Expert 5K+
P: 8,616
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
  2. 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
  3. GROUP BY [qryClinic Visit History Patient First Visit].PatientID, [qryClinic Visit History Patient First Visit].FirstOfVisit, [Clinic Visit History Sorted].VisitDate
  4. 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:

Expand|Select|Wrap|Line Numbers
  1. HAVING [Clinic Visit History Sorted].VisitDate BETWEEN [FirstOfVisit] AND DateAdd("m",1,[FirstOfVisit]);
to

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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 'FirstMonthsVisits' 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):
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim RS_Clone As DAO.Recordset, intGap As Integer
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyRS = MyDB.OpenRecordset("tblOfficeVisits", dbOpenDynaset)
  6.  
  7. Set RS_Clone = Me.RecordsetClone
  8.  
  9. MyRS.MoveFirst
  10. RS_Clone.Move 1         'move to 2nd Record
  11.  
  12. Do While Not RS_Clone.EOF
  13.   intGap = DateDiff("d", MyRS![Visit], RS_Clone![Visit])
  14.     MyRS.MoveNext       'MyRS must be in sync with RS_Clone
  15.       MyRS.Edit
  16.         MyRS![Gap] = intGap
  17.       MyRS.Update
  18.       MyRS.MovePrevious      'move to Original position
  19.   MyRS.MoveNext: RS_Clone.MoveNext
  20. Loop
  21.  
  22.  
  23. RS_Clone.Close
  24. MyRS.Close
  25. Set RS_Clone = Nothing
  26. Set MyRS = Nothing
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Client        Visit             Gap
  2. 1           1/1/2007    
  3. 1           1/28/2007       27
  4. 1           2/15/2007       18
  5. 1           3/12/2007       25
  6. 1           4/19/2007       38
  7. 1           6/2/2007       44
  8. 1           7/14/2007       42
  9. 1           8/26/2007       43
  10. 1           9/30/2007       35
  11. 1          11/23/2007       54
  12. 1          12/31/2007       38
  13. 1           1/3/2008        3
Sep 18 '07 #4

P: 23
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 19 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.