[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Compare Records

Hello:

I have a series of records that list the person and their appointment time. I am trying to do is build a query that lists individual who had multiple appointments within 45 days.

John Smith              10/7/2009 17:51
Mike Don              4/18/2010 4:11
Mike Don              11/13/2010 23:44
Abe Abel              8/1/2010 18:19
Abe Abel              7/13/2010 12:42
Abe Abel               6/24/2010 08:34
Marshall Tucker     7/27/2010 10:30
Kim Dale               8/4/2010 22:28

Thanks
Dan
0
RecipeDan
Asked:
RecipeDan
  • 3
1 Solution
 
peter57rCommented:
Need a better definition.
Do you mean.... for each appointment, did this person have an appointment within the 45 days prior to this one?

or are you specifying a particular 45 day period in which you are looking for people with more than one appointment in that period?
0
 
peter57rCommented:
For the first meaning you can create a query based on your table and add an extra column which is your version of..


Dcount("*", "tablename", "Personname='" & Personname & "' and apptDate <# " & format(ApptDate, "yyyy-mm-dd") & "# and apptdate>=# " & format(apptdate-45, "yyyy-mm-dd") & "#")

set the criteria for this column to
>0
0
 
RecipeDanAuthor Commented:
Hi Peter57:

What I am looking at doing is take the earliest appointment and see if the person returned within 45 days.

For example Abe Abel had an appointment on 6/24/2010 08:34 that was his first appointment . He had two additional appointments on 7/13/2010 12:42 and 8/1/2010 18:19. So he would show on the list because his two additional appointments were within 45 days of the initial one.  


0
 
peter57rCommented:
To see if the person returned in next 45 days you can modify the expression to..

Dcount("*", "tablename", "Personname='" & Personname & "' and apptDate ># " & format(ApptDate, "yyyy-mm-dd") & "# and apptdate<=# " & format(apptdate+45, "yyyy-mm-dd") & "#")

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now