Splitting a record by time stamps

[cmatthews]

Hello,

Brand new to the forum and newish to access. I have a query to look for data that includes a start and end timestamp as seen below.

shift_date	shift_ident	equip_ident	start_timestamp	end_timestamp	STATUS_CODE	Duration
10/03/2015	2	2555	5:30:00 AM	7:20:00 AM	MEC	6600

Im looking to break up the data into hours starting on half past the hour. In excel I'm attempting to see how much of this particular status code occurs during each hour interval. The current query shows all 6600seconds under 5.30am. I would like it to show 5.30am to 6.30am and duration as 3600seconds. Then create a second record below from 6.30am to 7.20am and with a duration of 3000seconds.

I have been told I could use code around the start and end timestamps to create this.

I'm currently using access 2003 and do most of the work in design view as I'm not familiar with VB.

Any help would be great. Excuse the lack of programming terminology.

Thanks

[orange]

Could you describe to readers in simple terms, what the business rationale for this is?
Many times people want total time to do such and such.

[NTC]

you will want to research the DateAdd method along with probably DateSerial - - - you will find good documentation and much Q&Q... then using these methods in a query one can calculate what you need.... they haven't changed since 2003 or before and are universal so you don't have to worry about any version specific issues......

[cmatthews]

I have thousand of records, with multiiple equipment in multiple status codes. I'm trying to see how much time is allocated to each status code for each hour across all equipment IDs.
Example
Between 5.30am and 6.30am in start timestamp there may be 3 equipment id's that had a duration of 20 min and 1with a duration of 6 hours. I need the records to be split so when I group the data in excel it will show 5.30am to 6.30am - 2hours, then 6.30am-7.30am - 1hour, 7.30am to 8.30am - 1 hour etc. So that 6hours is broken down into an hour per time interval.


Does that make more sense?

[cmatthews]

Thank you very much for that advice Nic