find events that happened at least 2 weeks apart

[mvlubejk]

Take a look at the following sample dataset:

animal #		Date		detection at Stream mile
1		1/1/2013		100
1		1/2/2013		95
1		1/3/2013		95
1		1/4/2013		100
1		1/4/2013		95
1		2/6/2013		100
1		2/7/2013		110
1		3/12/2013		100
1		3/13/2013		100
2		1/1/2013		110
2		1/2/2013		90
2		1/10/2013		90
2		5/5/2013		100
2		5/7/2013		100
2		6/10/2013		100
2		6/11/2013		110

3		2/2/2013		100
3		2/3/2013		90
3		2/7/2013		100
3		2/14/2013		100
3		2/15/2013		90
3		2/20/2013		100

I would like to query out all animals that had at least one instance where they were detected at stream mile 100 followed by a detection at stream mile 100 that spanned 14 days or greater. For example, animal 1 was at stream mile 100 on 1/4/2013 and the next time at stream mile 100 was 2/6/2013. These dates are more than 2 weeks apart from each other so I would like all associated data for animal 1 to show up in my query. I would also expect the query to return no results for animal 3 because there are no gaps of 14 days or greater between successive detection's at stream mile 100.

The output I would like generated would be:

animal #		Date		Stream mile
1		1/1/2013		100
1		1/2/2013		95
1		1/3/2013		95
1		1/4/2013		100
1		1/4/2013		95
1		2/6/2013		100
1		2/7/2013		110
1		3/12/2013		100
1		3/13/2013		100
2		1/1/2013		110
2		1/2/2013		90
2		1/10/2013		90
2		5/5/2013		100
2		5/7/2013		100
2		6/10/2013		100
2		6/11/2013		110

I would be happy to explain in more detail if needed. Any help is appreciated.

[June7]

Won't be simple because the criteria for selecting a record depends on value in another record of same table. Try:

SELECT * FROM table WHERE [animal #] IN (SELECT [animal #] FROM table WHERE Not DLookup("[animal #]", "table", "[stream mile]=100 AND [animal #]=" & [animal #] & " AND [Date] >=" & [Date]+14) Is Null);

EDIT: Nope, doesn't work. Still trying. Ooops, 'successive' is key criteria. Really gets complicated. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

This seems to work:

query 1: Mile100
SELECT Table1.[animal #], Table1.Date, Table1.Mile FROM Table1 WHERE (((Table1.Mile)=100));

query 2: GetPriorDate
SELECT (SELECT TOP 1 Dupe.Date FROM Mile100 AS Dupe WHERE Dupe.[animal #]=Mile100.[animal #] AND Dupe.Date<Mile100.Date ORDER BY Dupe.Date DESC) AS PriorDate, * FROM Mile100;

query 3:
SELECT * FROM table1 WHERE [animal #] IN (SELECT GetPriorDate.[animal #] FROM GetPriorDate WHERE [Date]-PriorDate >=14);


Advise no spaces and special characters/punctuation (underscore is exception) in naming convention. Should not use reserved words as names - Date is a reserved word.

[NoellaG]

Create the following groupsquery (change the table and foeld names according to your table) and save it, then use it to link to your table on the animalnr.

SELECT distinct AnimalNr
FROM tblTest
GROUP BY AnimalNr, StreamMile
having Max(DetectionDate) - Min(DetectionDate) > 14 ;

Carefull in your example animal 3 will be returned bacause it was spotted on StreamMile 100 on 2/2 and 2/20 which is more then 14 days apart.

Success
Noëlla

[mvlubejk]

It looks like the response from June7 is going to work but I'm having trouble with how long the final step is taking in my large dataset (currently working on condensing the results). In response to "Careful in your example animal 3 will be returned because it was spotted on StreamMile 100 on 2/2 and 2/20 which is more then 14 days apart"...I do not want this return because I only want successive detection's at 100 to be spaced by at least 14 days. as you can see, the most recent detection to 2/20 was on 2/14 so there are no gaps between successive detection's for animal 3 at 100 that are 14 days or greater.

Create the following groupsquery (change the table and foeld names according to your table) and save it, then use it to link to your table on the animalnr.

SELECT distinct AnimalNr
FROM tblTest
GROUP BY AnimalNr, StreamMile
having Max(DetectionDate) - Min(DetectionDate) > 14 ;



Success
Noëlla

[June7]

Thanks for the rep. Regarding your comment in the rep, instead of 'making' the table each time the procedure is run, create a permanent table and append records then purge when done. This is a 'temp' table because the records are temporary. Making a table is a db design modification whereas just inserting/deleting records is not.