Hi
I'm trying to organise the data shown below so that it calculates the number of minutes a particular task was carried out for, within in each hour, over a 12 hour shift from 06:00 to 18:00. See raw data below:
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>
This is the format I'd like to convert the data to using a query:
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="12"></colgroup>
So, for example, in the first hour from 6:00 to 7:00, there were 3 different activities which took 4 minutes, 2 minutes and 54 minutes respectively, all together making up the 60 minutes.
Many thanks for your help.
I'm trying to organise the data shown below so that it calculates the number of minutes a particular task was carried out for, within in each hour, over a 12 hour shift from 06:00 to 18:00. See raw data below:
| Date | ID | Last Name | First Name | Actual Start | Actual End | Activity description |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 16:34 | 19/04/2015 16:50 | Breaks |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 14:35 | 19/04/2015 16:34 | D1 Marshalling |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 16:50 | 19/04/2015 18:00 | D1 Marshalling |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 14:05 | 19/04/2015 14:35 | Breaks |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 10:18 | 19/04/2015 14:05 | D1 Marshalling |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 06:06 | 19/04/2015 10:04 | DC1 Loading Other |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 10:04 | 19/04/2015 10:18 | Breaks |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 05:52 | 19/04/2015 06:04 | Briefing |
| 19/04/2015 | 6921798 | Smith | John | 19/04/2015 06:04 | 19/04/2015 06:06 | D1 Stock |
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>
This is the format I'd like to convert the data to using a query:
| Date | ID | Last Name | First Name | Activity description | 06:00 | 07:00 | 08:00 | 09:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 |
| 19/04/2015 | 6921798 | Smith | John | Briefing | 4 | |||||||||||
| 19/04/2015 | 6921799 | Smith | John | D1 Stock | 2 | |||||||||||
| 19/04/2015 | 6921800 | Smith | John | DC1 Loading Other | 54 | 60 | 60 | 60 | 4 | |||||||
| 19/04/2015 | 6921801 | Smith | John | Breaks | 14 | |||||||||||
| 19/04/2015 | 6921802 | Smith | John | D1 Marshalling | 42 | 60 | 60 | 60 | 5 | |||||||
| 19/04/2015 | 6921803 | Smith | John | Breaks | 30 | |||||||||||
| 19/04/2015 | 6921804 | Smith | John | D1 Marshalling | 25 | 60 | 34 | |||||||||
| 19/04/2015 | 6921805 | Smith | John | Breaks | 16 | |||||||||||
| 19/04/2015 | 6921806 | Smith | John | D1 Marshalling | 10 | 60 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="12"></colgroup>
So, for example, in the first hour from 6:00 to 7:00, there were 3 different activities which took 4 minutes, 2 minutes and 54 minutes respectively, all together making up the 60 minutes.
Many thanks for your help.
