Grouping by date/time intervals

[Sampaio]

Hello guys, I'm trying to group some data according to the time interval between observations, in my particular case i need to group events that are 5 or less minutes apart from each other. I'm new to database work so forgive me if this has a very basic solution but I've been trying to do this for a long time and can't get it quite right, any help would be much appreciated Thanks

[June7]

Nothing basic about this and doubt will be simple. Sounds like you need to calculate the elapsed time between records. This requires each record pulling value from previous record. Review example in http://allenbrowne.com/subquery-01.html#AnotherRecord

Provide example raw data and desired output. If you want to provide db for analysis, follow instructions at bottom of my post.

[Sampaio]

Thank you for the answer, my db is huge (more than 5x10^5 records so i will just post some records here for example
what I want my output to be is grouping of all the sightings of the same species with the same sex and in the same life stage that were made with a time interval of 5 minutes or less between each other. here is the table. Once again thank you so much for the help, I'll see if i adapt the scripts in your example

ID	Date/Time	Sighting ID	Sighting Quantity	Species	Trap Station Name	Sex	Life stage	Note
1	09/01/2011 10:11:42	14927	1	Homo sapiens (on foot)	NV0128 Rogić dolina	Male	Adult
2	09/01/2011 10:11:42	14928	1	Homo sapiens (on foot)	NV0128 Rogić dolina	Male	Adult
3	09/01/2011 10:11:42	14929	1	Homo sapiens (on foot)	NV0128 Rogić dolina	Male	Adult
4	09/01/2011 17:51:39				NV0128 Rogić dolina
5	09/01/2011 17:51:39				NV0128 Rogić dolina
6	09/01/2011 17:51:39				NV0128 Rogić dolina
7	10/01/2011 12:37:46				NV0128 Rogić dolina
8	10/01/2011 12:37:46				NV0128 Rogić dolina
9	10/01/2011 12:37:47				NV0128 Rogić dolina
10	11/01/2011 01:50:37				NV0128 Rogić dolina
11	11/01/2011 01:50:37				NV0128 Rogić dolina
12	11/01/2011 01:50:37				NV0128 Rogić dolina
13	11/01/2011 13:18:36				NV0128 Rogić dolina
14	11/01/2011 13:18:36				NV0128 Rogić dolina
15	11/01/2011 13:18:36				NV0128 Rogić dolina
16	11/01/2011 13:18:51				NV0128 Rogić dolina
17	11/01/2011 13:18:51				NV0128 Rogić dolina
18	11/01/2011 13:18:51				NV0128 Rogić dolina
19	11/01/2011 13:19:16				NV0128 Rogić dolina
20	11/01/2011 13:19:16				NV0128 Rogić dolina
21	11/01/2011 13:19:16				NV0128 Rogić dolina
22	12/01/2011 06:45:40	14931	1	Sciurus vulgaris	NV0128 Rogić dolina		Adult
23	12/01/2011 06:45:40	14932	1	Sciurus vulgaris	NV0128 Rogić dolina		Adult
24	12/01/2011 06:45:40	14930	1	Sciurus vulgaris	NV0128 Rogić dolina		Adult
25	12/01/2011 13:51:34	14933	1	Vulpes vulpes	NV0128 Rogić dolina		Adult
26	12/01/2011 13:51:34	14934	1	Vulpes vulpes	NV0128 Rogić dolina		Adult
27	12/01/2011 13:51:34	14935	1	Vulpes vulpes	NV0128 Rogić dolina		Adult
28	13/01/2011 07:50:02				NV0128 Rogić dolina
29	13/01/2011 07:50:02				NV0128 Rogić dolina
30	13/01/2011 07:50:02				NV0128 Rogić dolina
31	14/01/2011 05:01:53				NV0128 Rogić dolina
32	14/01/2011 05:01:53				NV0128 Rogić dolina
33	14/01/2011 05:01:53				NV0128 Rogić dolina
34	14/01/2011 05:54:51				NV0128 Rogić dolina
35	14/01/2011 05:54:51				NV0128 Rogić dolina
36	14/01/2011 05:54:51				NV0128 Rogić dolina
37	14/01/2011 10:23:32				NV0128 Rogić dolina
38	14/01/2011 10:23:32				NV0128 Rogić dolina
39	14/01/2011 10:23:32				NV0128 Rogić dolina
40	14/01/2011 10:43:05				NV0128 Rogić dolina
41	14/01/2011 10:43:05				NV0128 Rogić dolina
42	14/01/2011 10:43:06				NV0128 Rogić dolina
43	14/01/2011 14:30:42				NV0128 Rogić dolina
44	14/01/2011 14:30:42				NV0128 Rogić dolina
45	14/01/2011 14:30:42				NV0128 Rogić dolina
46	14/01/2011 15:51:24	14936	1	Meles meles	NV0128 Rogić dolina		Adult
47	14/01/2011 15:51:24	14938	1	Meles meles	NV0128 Rogić dolina		Adult
48	14/01/2011 15:51:24	14937	1	Meles meles	NV0128 Rogić dolina		Adult
49	14/01/2011 15:51:39				NV0128 Rogić dolina
50	14/01/2011 15:51:39				NV0128 Rogić dolina
51	14/01/2011 15:51:39				NV0128 Rogić dolina
52	14/01/2011 17:32:07	14941	1	Meles meles	NV0128 Rogić dolina		Adult
53	14/01/2011 17:32:07	14939	1	Meles meles	NV0128 Rogić dolina		Adult
54	14/01/2011 17:32:07	14940	1	Meles meles	NV0128 Rogić dolina		Adult
55	15/01/2011 06:03:32	14944	1	Vulpes vulpes	NV0128 Rogić dolina		Adult
56	15/01/2011 06:03:32	14942	1	Vulpes vulpes	NV0128 Rogić dolina		Adult
57	15/01/2011 06:03:32	14943	1	Vulpes vulpes	NV0128 Rogić dolina		Adult

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[Sampaio]

So a friend that knows a bit of SQL gave me some hints and we got to this script but for some reason it keeps saying there is a syntax error but I just can't seem to figure out what is missing here, can anyone give me a fresh insight please?

SELECT FIX(DATEDIFF("n",DateTime,"01/01/1901 00:00:00")/5)*5
,MAX("Sighting Quantity")
,"sex"
,"Species Common Name"
,"Life stage"
,"Trap Station Name"


FROM "Sightings: Table"


GROUP BY
FIX(DATEDIFF("n",DateTime,"01/01/1901 00:00:00")/5)*5
,"sex"
,"Species Common Name"
,"Life stage"
,"Trap Station Name"

[pbaldy]

The field and table names should be surrounded with square brackets, not quotes.

[Sampaio]

Thank you, with that and some other syntax modifications i managed to solve my problem