Hello,
I'm not sure if what I am trying to do here is simple or not and my brain hasn't really came up with a way to do it so here goes:
I have a table which holds my data like this:
I'm looking to copy all records to another table which would look like this:
Amongst other things what this table will be used for is to report the cost grouped by week or by month, that's why I think I need to split each record before getting to the report or query building stage.
There is hundreds of bookings, my list above is just to hopefully show what I'd like to achieve.
Can anyone think of a way that I can achieve this?
Many thanks,
Malcolm
I'm not sure if what I am trying to do here is simple or not and my brain hasn't really came up with a way to do it so here goes:
I have a table which holds my data like this:
Booking | Arrival (D/M/YYYY) | Departure (D/M/YYYY) | Nights | Cost |
1 | 1/1/2021 | 3/1/2021 | 2 | 100 |
2 | 2/1/2021 | 5/1/2021 | 3 | 120 |
I'm looking to copy all records to another table which would look like this:
Booking | Arrival (D/M/YYYY) | Departure (D/M/YYYY) | Nights | Cost |
1 | 1/1/2021 | 2/1/2021 | 1 | 50 |
1 | 2/1/2021 | 3/1/2021 | 1 | 50 |
2 | 2/1/2021 | 3/1/2021 | 1 | 40 |
2 | 3/1/2021 | 4/1/2021 | 1 | 40 |
2 | 4/1/2021 | 5/1/2021 | 1 | 40 |
Amongst other things what this table will be used for is to report the cost grouped by week or by month, that's why I think I need to split each record before getting to the report or query building stage.
There is hundreds of bookings, my list above is just to hopefully show what I'd like to achieve.
Can anyone think of a way that I can achieve this?
Many thanks,
Malcolm