Find duplicate records within same year (1 Viewer)

[asid]

Hi
I have generated a table of dublicates records.
I want to now filter all of those duplciates (people) that have been contacted more than once wihin a year.

Eample

personID contactDate
100 01/10/2012
100 06/11/2012
100 11/11/2017
show this as the first two reocords were withn the same year

101 01/12/2012
101 01/15/2014
dont show

How do I do this, maybe a query is not the best way?

 

[JHB]

..
How do I do this, maybe a query is not the best way?

A query is the perfect way:

• First create a query, which find all the person who are contacted more as 1 time per year, call the query "ContactedTimesPerYear"
• Then create a query which list the person and the date, joined by the first query on the personId and the Year(contactDate). Remark the query can only be viewed in SQL view.

Sample database is attached, run the query "FinalQuery".

 

[asid]

Thank you, close but when I say within a year, that is not the same calendar year but a year from the previous contact.
So this should show as a the second contact was only a month later?

personID contactDate
105 15/12/2008
105 1/01/2009
105 1/01/2017

 

[plog]

First, "within a year" is hard to define at the edges. What date is 1 year after February 29, 2016? February 28th or March 1st?

So, using JHB's database, what I did is wrote 2 queries to determine the days difference between a contact's last contact date. The first determines the last date prior to the current one:

SELECT T.personID, T.contactDate, (SELECT Max(tblPerson.contactDate) FROM tblPerson where tblPerson.personID = T.personID AND tblPerson.contactDate<T.contactDate) AS LastContactDate
FROM tblPerson AS T;

PAste the above SQL into Access and name it 'sub1'. Then on top of that, I built another query to determine the total days between those two:

SELECT sub1.personID, sub1.contactDate, sub1.LastContactDate, DateDiff("d",[LastcontactDate],[ContactDate]) AS LastContactDays
FROM sub1;

Paste that into a new query and run it. Then add your criteria and GROUP appropriately to get the exact results you want.

 

[asid]

Thanks that did work. In the end I used vba to loop through a record set, there were several million records and the queries struggled.