Grouping in a query

[Mick_Hart]

Okay, so i have a list times throughout the day. These times range from midnight to midnight, occuring in minute intervals.

I need to build a query, so that i change -

Time | Ammount
00:30 | 1
00:31 | 6
08:41 | 19
08:59 | 1
15:00 | 6
15:31 | 7

Into this

Time | Ammount
00:30 - 00:59 | 7
08:30 - 08:59 | 21
15:00 - 15:29 | 6
15:30 - 15:59 | 7


I havent got a clue as to where to start with this, so any help would be greatly appreciated

 

[Corticus]

Enter this as SQL:

SELECT IIf(Right([time],2)>=30,Left([time],2) & ":30 - " & Left([time],2) & ":59",Left([time],2) & ":00 - " & Left([time],2) & ":29") AS TimeGroup, Sum(tblTimes.Amount) AS SumOfAmount
FROM tblTimes
GROUP BY IIf(Right([time],2)>=30,Left([time],2) & ":30 - " & Left([time],2) & ":59",Left([time],2) & ":00 - " & Left([time],2) & ":29");

I used tblTimes as the table name, adjust to suit.

 

[Mick_Hart]

HUZZAH!!!!

Excellent, Corticus, you quite literally are 'the man'