Copy Multi-Day Records to Create List of Dates (1 Viewer)

Malcolm17

Member
Local time
Today, 09:02
Joined
Jun 11, 2018
Messages
107
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:
BookingArrival (D/M/YYYY)Departure (D/M/YYYY)NightsCost
11/1/20213/1/20212100
22/1/20215/1/20213120

I'm looking to copy all records to another table which would look like this:
BookingArrival (D/M/YYYY)Departure (D/M/YYYY)NightsCost
11/1/20212/1/2021150
12/1/20213/1/2021150
22/1/20213/1/2021140
23/1/20214/1/2021140
24/1/20215/1/2021140

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
 
Open 2 recordsets, one on each table (the second for append only). Use a For/Next loop on the dates, and within that loop add a record to the other table.
 
I should have mentioned that the For/Next loop would be within a loop of the first table recordset.
 
Thanks for that guys, I will have a look at it. Its not something I have done before so I am hoping there will be a sample code on your blog Uncle Gizmo. If not do either of you have a sample code I could adapt?

Many thanks,

Malcolm
 
I assume UG has loop code on his site. Here's a quick and dirty demo of the For/Next loop:

Code:
Public Function AddDates()
  Dim dteStartDate As Date
  Dim dteEndDate As Date
  Dim dteDate As Date

  dteStartDate = #5/1/2021#
  dteEndDate = #5/10/2021#

  For dteDate = dteStartDate To dteEndDate
    Debug.Print dteDate
  Next dteDate

End Function
 
No problem, post back if you get stuck.
 
Hi. You might take a look at the links I posted in this other thread, in case it helps.

 
you can do this with a query and utilising a reference table and query

tblRef
Num
0
1
2
3
4
5
6
7
8
9

then the query (adjust for max number of days), this one works from 0 to 999, add another alias for 0 to 9999 etc

QryRef

Code:
SELECT CLng([R1].[Num]+([R2].[Num]*10)+([R3].[Num]*100) AS [Nums]
FROM tblRef AS R1, tblRef AS R2, tblRef AS R3

Now for your table - this is a select query, you can easily change it to a make table or insert query, but depending on need, perhaps all you need is a select query instead of a table

Code:
SELECT Booking, [Arrival (D/M/YYYY)]+[Nums] AS Arrival, [Arrival]+1 AS Departure, 1 AS Nights, [Cost]/([Departure (D/M/YYYY)]-[Arrival (D/M/YYYY)]) AS DayCost
FROM tblBookings, qryRef
WHERE Nums<[Nights]
ORDER BY tblBookings.Booking, [Arrival (D/M/YYYY)]+[Nums]

which produces this result
Query32

BookingArrivalDepartureNightsDayCost
1​
01/01/2021​
02/01/2021​
1​
50​
1​
02/01/2021​
03/01/2021​
1​
50​
2​
02/01/2021​
03/01/2021​
1​
40​
2​
03/01/2021​
04/01/2021​
1​
40​
2​
04/01/2021​
05/01/2021​
1​
40​


Advise not to use spaces and non alpha numeric characters in your field names
 
can be done in a query alright.
without using "extra" table.
the queries are "numbered" (to see each steps on how it is done).
you should be able to follow if you open each query in design view.
your final query is "3_Final".
 

Attachments

you can use your first query rather than the table if you prefer. I used to do it that way but found it can be slower for very large numbers

Agree to not using nights (using datediff instead) since this should be a calculated value, not stored in a table
 
I wonder whether filling a table for each day of a booking is efficient or not, compared with just using the start/end dates. I can see some benefits. does anyone have practical experience of hotel reservation/plant hire systems or similar that would need a "block booking" mechanism
 
don't disagree - it is the way I do it for a booking app I created. But without knowing exactly what the OP requires and potential issues around missing dates/different rates for different days etc I didn't suggest it.
 

Users who are viewing this thread

Back
Top Bottom