Create Records in a New table based on intervals in a range

[jamessl]

I have a Booking table (fields - Employee number, Date, Start time, Finish time) in which an employee enters a start and finish time for when they will be booking some equipment - eg Employee Number - 1, Date - 23/07/02, Start time - 10:00, Finish time - 18:00.

How would I get a query (Code??) to automatically create records in a different table (Booking Ranges) on the given Employee Number and Date, so that a record for each individual hour between the Start and Finish Time values was created?

ie in the new table have the following records

employee no Date Hour interval

1 23/7/02 10:00
1 23/7/02 11:00
1 23/7/02 12:00

up to

1 23/7/02 18:00

I think it should be possible but I'm not sure what the best tactic is...Perhaps Code within the query which calculates the number of hours within the range, then adds 1 hour to the Start time until it reaches the Finish time, creating a record within each cycle of the loop (if that is possible)? Or is there a simpler solution?

 

[raskew]

You've already envisioned the solution. Just need to build it.
Try this for starters.

Function HourToHour(startdate As Date, starttime As Date, _
enddate As Date, endtime As Date)
'*******************************************
'Name:      HourToHour (Function)
'Purpose:   Increment date/time field one hour
'           at a time.
'Inputs:    from debug window:
'           ? HourToHour(#07/23/02#, #10:00#, #07/23/02#, #18:00#)
'Output:    7/23/02 10:00:00 AM
'           7/23/02 11:00:00 AM
'           7/23/02 12:00:00 PM
'           7/23/02 1:00:00 PM
'           7/23/02 2:00:00 PM
'           7/23/02 3:00:00 PM
'           7/23/02 4:00:00 PM
'           7/23/02 5:00:00 PM
'           7/23/02 6:00:00 PM
'*******************************************

Dim dteFirst As Date, dteLast As Date

dteFirst = startdate + starttime
dteLast = enddate + endtime
Do While dteFirst <= dteLast
   Debug.Print dteFirst
   'Place your add-record process here!
   dteFirst = DateAdd(&quot;h&quot;, 1, dteFirst)
Loop
End Function

 

[jamessl]

Thanks, will be a while before I can integrate this into my ever evolving project, but it looks perfect..