How can I summarize a table based on a specific time interval in a Date/Time column?

[Ducknut]

Hi all, I was thinking that an expert like FishVal might be interested in solving this one (based on his name).

I have several fish tagged with radio telemetry tags. These tags send a signal to a telemetry receiver, which I download and import into an Access 2003 database. So I have a table with the following columns: an AutoNumber, Station, Datetime, and Code (the tag number). Here is some sample data:

ID Station Datetime Code
3421 R11 30/03/2007 11:37:25 96
3306 R11 30/03/2007 11:42:02 96
1192 R11 30/03/2007 12:01:24 96
281 R11 30/03/2007 12:01:31 96
4804 R11 30/03/2007 12:02:02 96
1201 R11 30/03/2007 12:02:39 96
4810 R11 30/03/2007 12:06:37 96
289 R11 30/03/2007 12:11:08 96
1210 R11 30/03/2007 12:12:46 96
4817 R11 30/03/2007 12:16:58 96
296 R11 30/03/2007 12:21:26 96
1217 R11 30/03/2007 12:23:06 96
4826 R11 30/03/2007 12:27:33 96
4834 R11 30/03/2007 12:38:08 96
4844 R11 30/03/2007 12:48:32 96
4853 R11 30/03/2007 12:57:42 96
301 R11 30/03/2007 12:57:58 96
305 R11 30/03/2007 13:07:50 96
4861 R11 30/03/2007 13:07:55 96
4870 R11 30/03/2007 13:18:07 96
310 R11 30/03/2007 13:28:27 96
4879 R11 30/03/2007 13:28:34 96
4887 R11 30/03/2007 13:40:00 96
4894 R11 30/03/2007 13:49:11 96
4902 R11 30/03/2007 13:59:48 96
4915 R11 30/03/2007 14:19:46 96
4921 R11 30/03/2007 14:31:04 96
321 R11 30/03/2007 14:39:34 96
4928 R11 30/03/2007 14:40:52 96
325 R11 30/03/2007 14:43:30 96
4936 R11 30/03/2007 14:50:00 96
4945 R11 30/03/2007 15:02:23 96
334 R11 30/03/2007 15:05:10 96
4954 R11 30/03/2007 15:10:37 96
4961 R11 30/03/2007 15:20:20 96
337 R11 30/03/2007 15:21:07 96
342 R11 30/03/2007 15:23:13 96
4970 R11 30/03/2007 15:33:10 96
346 R11 30/03/2007 15:35:43 96
4978 R11 30/03/2007 15:41:22 96
350 R11 30/03/2007 15:46:23 96
4986 R11 30/03/2007 15:52:14 96
357 R11 30/03/2007 15:53:00 96
4995 R11 30/03/2007 16:02:02 96

The problem that I’m having is that sometimes a fish will hang out next to a station for a long period of time (creating a record every few seconds). I want to summarize the data so that if detections are less than 15 minutes apart (for 1 tag at one station) it just shows the first record and the last record before the interval that is greater than 15 minutes. So that the 45 records I pasted above could be summarized into three records like this:

Station Start Time EndTime Code
R11 30/03/2007 11:37:25 30/03/2007 11:42:02 96
R11 30/03/2007 12:01:24 30/03/2007 13:59:48 96
R11 30/03/2007 14:19:46 30/03/2007 16:02:02 96

When I first started this project it took a few minutes to do manually. Unfortunately, I now have half a million records with possibly another million records before the batteries in the tags fail.

Any information would be greatly appreciated and I thank you all in advance.

Sincerely,

DuckNut.

[hjozinovic]

Hi DuckNut,

Could you be more specific about the result you want to get?

Now I'm thinking:
a) You need to write to your table only records that are 15 minutes apart?
b) You want to have in your tables all records writen in every few seconds, and then to run a report that would only contain records that are time-separated enough?

h.

[Ducknut]

Hi h., Thanks for the response.

To answer you question, I need all the raw data in one table and a query or report that will summarize it, where each row lists the start and stop time of a period where the fish was detected regularly (i.e., the fish was detected at a minimum of every 15 minutes between the start and stop time).

What I need is a query that will list the first time the fish is detected (i.e., the fish was not detected in the 15 minute time period before that detection) and the last time the fish was detected (i.e., the fish was not detected in the 15 minute time period after that detection). I should be able to get both these values by somehow subtracting the date (and time) of one detection from the next detection (in chronological order) to find out if the detections are more than 15 minutes apart. In other words, if the fish wasn’t detected for 15 minutes (or more) any subsequent detections would be included in the next row of the summary table. To help clarify, I’ve included the raw sample data from the first post, but put in a sequential number to help explain:

Sequential ID Station Datetime Code
1 3421 R11 30/03/2007 11:37:25 96
2 3306 R11 30/03/2007 11:42:02 96
3 1192 R11 30/03/2007 12:01:24 96
4 281 R11 30/03/2007 12:01:31 96
5 4804 R11 30/03/2007 12:02:02 96
6 1201 R11 30/03/2007 12:02:39 96
7 4810 R11 30/03/2007 12:06:37 96
8 289 R11 30/03/2007 12:11:08 96
9 1210 R11 30/03/2007 12:12:46 96
10 4817 R11 30/03/2007 12:16:58 96
11 296 R11 30/03/2007 12:21:26 96
12 1217 R11 30/03/2007 12:23:06 96
13 4826 R11 30/03/2007 12:27:33 96
14 4834 R11 30/03/2007 12:38:08 96
15 4844 R11 30/03/2007 12:48:32 96
16 4853 R11 30/03/2007 12:57:42 96
17 301 R11 30/03/2007 12:57:58 96
18 305 R11 30/03/2007 13:07:50 96
19 4861 R11 30/03/2007 13:07:55 96
20 4870 R11 30/03/2007 13:18:07 96
21 310 R11 30/03/2007 13:28:27 96
22 4879 R11 30/03/2007 13:28:34 96
23 4887 R11 30/03/2007 13:40:00 96
24 4894 R11 30/03/2007 13:49:11 96
25 4902 R11 30/03/2007 13:59:48 96
26 4915 R11 30/03/2007 14:19:46 96
27 4921 R11 30/03/2007 14:31:04 96
28 321 R11 30/03/2007 14:39:34 96
29 4928 R11 30/03/2007 14:40:52 96
30 325 R11 30/03/2007 14:43:30 96
31 4936 R11 30/03/2007 14:50:00 96
32 4945 R11 30/03/2007 15:02:23 96
33 334 R11 30/03/2007 15:05:10 96
34 4954 R11 30/03/2007 15:10:37 96
35 4961 R11 30/03/2007 15:20:20 96
36 337 R11 30/03/2007 15:21:07 96
37 342 R11 30/03/2007 15:23:13 96
38 4970 R11 30/03/2007 15:33:10 96
39 346 R11 30/03/2007 15:35:43 96
40 4978 R11 30/03/2007 15:41:22 96
41 350 R11 30/03/2007 15:46:23 96
42 4986 R11 30/03/2007 15:52:14 96
43 357 R11 30/03/2007 15:53:00 96
44 4995 R11 30/03/2007 16:02:02 96

So in the summary table it would include the date (and time) from Record #1 (because that is the first record for that fish). The end time for the first row of the summary table would equal the date (and time) from Record #2 because there is more than 15 minutes between Record #2 and Record #3. Like this:

Station StartTime EndTime Code
R11 30/03/2007 11:37: 25 30/03/2007 11:42:02 96

The next record of the summary table would be the date and time of Record #3 (because it wasn’t detected in the 15 minutes prior to that detection) and the date and time of Record #25 (because it wasn’t detected in the 15 minute period after that detection). So my summary table would look like this:

Station Start Time EndTime Code
R11 30/03/2007 11:37:25 30/03/2007 11:42:02 96
R11 30/03/2007 12:01:24 30/03/2007 13:59:48 96

Similarly, the third record of the summary would include the date and time in Record #26 and the last record for the fish (Record #44):

Station Start Time EndTime Code
R11 30/03/2007 11:37:25 30/03/2007 11:42:02 96
R11 30/03/2007 12:01:24 30/03/2007 13:59:48 96
R11 30/03/2007 14:19:46 30/03/2007 16:02:02 96

So all 44 records from the raw data is summarized down to those three lines.

I’m sorry that these are turning into some fairly long posts, but I’m totally stumped, and I can’t think of a different way of explaining my problem.

Thanks again for any effort.

DuckNut.