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

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

Compare Records


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

  • 3
1 Solution
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?
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
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.  

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") & "#")


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