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?
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?