Grouping time in intervals using Partition Function

[clarencelai]

I have a field (date_time) containing various timings (short time format) and another field (sold) containing the number of items customers has purchased.

I would like to sum the number of items sold during the day in 15 mins interval. The desired outcome will be 4:00PM:4:15PM, 10 sold; 4:16PM:4:30PM, 15 sold, and so on.

I tried to achieve it by using PARTITION in query to no avail.

Example 1:

Range: Partition([Date_Time],TimeSerial(0,0 ,0),TimeSerial( 23,59,0),TimeSe rial(0,15,0))

I encountered an error with the above - Invalid Procedure Call

Example 2:

Range: Partition(Minut e([Date_Time]),0,1440,15)

Result
Range TotalSold
0: 14 143
15: 29 128
30: 44 110
45: 59 163

In example 2, the result though was in 15 mins interval but not in the time range I wanted.

Please see the screenshots attached for the parameters I have entered in my queries.

Any help will be very much appreciated.

Thank you.
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=87 25[/IMGnothumb]

[zmbd]

In the greyish area next to your tables in the query editor
1) Right click
2) Select SQL View in the quick menu that opens
3) Copy the text shown, <ctrl><a>;<ctrl ><c>
4) Come back to this thread, and in the post box <ctrl><v>
5) Select the pasted text and click on the [CODE/] tool in the post formatting toolbar.

The SQL is much more helpful to use than the screen shots.

Also, please keep in mind, many of our experts are unable to d/l unrequested attachments as many of us help out from work and/or are using secured PCs.

[clarencelai]

SELECT Partition(Minute([Date_Time]),0,1440,15) AS Range, Sum([Transaction by DateTime and Ope].Sold) AS TotalSold
FROM [Transaction by DateTime and Ope]
GROUP BY Partition(Minute([Date_Time]),0,1440,15);

SELECT Partition([Date_Time],TimeSerial(0,0,0),TimeSerial(23,59,0),TimeSerial(0,15,0)) AS Range, Sum([Transaction by DateTime and Ope].Sold) AS TotalSold
FROM [Transaction by DateTime and Ope]
GROUP BY Partition([Date_Time],TimeSerial(0,0,0),TimeSerial(23,59,0),TimeSerial(0,15,0));

Thanks, ZMBD.

I have added the SQL above. The first one is example 2 and the latter, example 1.

[PhilOfWalton]

Try

SELECT CStr(Hour([date_time])) & ":" & CStr(Format((Minute([date_time])\15)*15,"00") & " to " & CStr(Hour([date_time])) & ":" & Format((Minute([date_time])\15)*15,"00")+14) AS Time_Slot, Sum(Sheet1.Sold) AS SumOfSold
FROM Sheet1
GROUP BY CStr(Hour([date_time])) & ":" & CStr(Format((Minute([date_time])\15)*15,"00") & " to " & CStr(Hour([date_time])) & ":" & Format((Minute([date_time])\15)*15,"00")+14);

I would add that sheet1 is your excel sheet so presumably this will convert to an Access table.

Basically it converts the minutes into 15 minute slots and adds 14 minutes to show the start and end of the slot.
Then a simple group by the time slot and a sum of the number sold.

Please check the data carefully to ensure the results are correct

Phil

[clarencelai]

Thanks a lot, Phil.

It works!

Clarence