Hi all,
I am using the SQL code below in an Access 2010 query to distinguish turtle capture events as "New" or "Recapture".
SELECT [tbl_Watamu Turtle Bycatch Data].[DateTime Caught], [tbl_Watamu Turtle Bycatch Data].[Tag 1], IIf([DateTime Caught]=DMin("[DateTime Caught]","tbl_Watamu Turtle Bycatch Data","[Tag 1]='" & [Tag 1] & "'"),"New","Recapture") AS [ReleaseType]
FROM [tbl_Watamu Turtle Bycatch Data];
A record in my main table represents a capture event. The first time a Tag appears, the associated capture event should be designated "New" in "ReleaseType" field. And whenever the same tag reappears/for all subsequent captures it should be designated "Recapture". However, this query returns "Recapture" ONLY for REcapture events in Years after the Year of first capture. This means that if a Tag appears for the first time in a capture event recorded on 01/Jan/2012, that capture event gets "New" in its "ReleaseType" field: Which is correct. Now problem is, if the 2nd/3rd/4th.. captures are in the same year, the query STILL returns "New" in its "ReleaseType" field. It only returns "Recapture" for the recapture events in 2013, 2014 etc. My date field is "DateTimeCaught" and follows the format: "dd,mm,yyyy hhmm", so I assume the query should be able to distinguish the dates to the hour.
How do I rectify this?
Thanks.