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

[Malcolm17]

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:

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

 

[pbaldy]

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.

 

[Uncle Gizmo]

I think you need to use a recordset loop. See my blog here for more information on record set loops:- https://www.niftyaccess.com/recordset-loops-how-to/

 

[pbaldy]

I should have mentioned that the For/Next loop would be within a loop of the first table recordset.

 

[Malcolm17]

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

 

[pbaldy]

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

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

 

[Malcolm17]

Cool, thank you for that, I will get to it tomorrow

 

[pbaldy]

No problem, post back if you get stuck.

 

[theDBguy]

Hi. You might take a look at the links I posted in this other thread, in case it helps.

Creating a Record for Each Customer for Each Period

I'm an advanced user of Access for someone in corporate finance, however a novice compared to those in technology. I've gotten by with simple queries and basic SQL language. I have a list of about 400 customers. I would like to create a record for each customer, for each month. For example...

www.access-programmers.co.uk

 

[CJ_London]

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

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

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

Booking	Arrival	Departure	Nights	DayCost
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

 

[arnelgp]

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".

 

[CJ_London]

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

 

[gemma-the-husky]

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

 

[CJ_London]

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.