Records in 15 mts interval 1

[jshanoo]

Hi all,

TrnkDt rnktm fld10 fld5
03/01/2004 00:05 2465 24
03/01/2004 00:10 760 26
03/01/2004 00:15 1489 12
03/01/2004 00:20 1158 17
03/01/2004 00:25 592 17
03/01/2004 00:30 1297 10
03/01/2004 00:35 1710 9
03/01/2004 00:40 2211 12
03/01/2004 00:45 546 17
03/01/2004 00:50 275 12
03/01/2004 00:55 220 9
03/01/2004 01:00 365 6
03/01/2004 01:05 1457 8
03/01/2004 01:10 413 6
03/01/2004 01:15 73 5
03/01/2004 01:20 220 3
03/01/2004 01:25 70 4
03/01/2004 01:30 454 7

please see teh above mentioned table.
i want a single query from which i can take sum for 15 mts interval. records are in 5 mts interval.
I am preferring single sql stmt to retrieve the results.
the database is ms access

Regards
John Philip


*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[formerTexan]

John
I don't believe you are going to get your results from a single query and I don't think there is an Access SQL function to so what you want. Having gotten that hurdle behind you, there are probably a few ways to approach this.

Probably the most satisfactory is going to be to first create a query to return the above recordset with the mktm field sorted in ascending order and open your recordset in VBA. Then loop through the recordset to sequentially get the sum of each record subset

Come back if you need help with the VBA code.

Cheers,
Bill

 

[johnwm]

Try the Top statement. I haven't tested this but something like:

Select Top 3 TrnkDt, rnktm, fld10, fld5 From myTable Order by rnktm Desc

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Golom]

I assume that your dates and times are both datetime fields. If so, try this
[blue][tt]
Select Count(*) As Records, SUM(fld10) As Sum10, SUM(fld05) As Sum05

From tbl

Group By Partition ( DateDiff ("n", (TrnkDt+rnktm), Date()), 0, 999999, 15 )

Order by Partition ( DateDiff ("n", (TrnkDt+rnktm), Date()), 0, 999999, 15 ) DESC
[/tt][/blue]

 

[Golom]

Needed to adjust the Partition ranges
[blue][tt]
SELECT MIN(TrnkDt) AS StartTime,
MAX(TrnkDt) AS EndTime,
Count(*) As [Entries],
SUM(fld10) AS Sum10,
SUM(fld05) AS Sum05

FROM tblTimes

GROUP BY Partition ( DateDiff ("n", (TrnkDt+rnktm), Date()), 1, 999999, 15 )

ORDER BY Partition ( DateDiff ("n", (TrnkDt+rnktm), Date()), 1, 999999, 15 ) DESC;
[/tt][/blue]

 

[jshanoo]

Hi Golom,
Thank you for the wonderful help and more of partition function. it was great.

I have given a * mark for your answer

Best Regards
John Philip

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[formerTexan]

And I found it educational as well.

Thanks, Golom