I am not a programmer or code writer but use Acess 2007 to store many records.
I have a hotel table of the basics of the reservation with most important Check IN and Check out Date . Room Nights.
What I need is to have this data appened to another table in the same data base.
I need every date that the Employee stays overnight in the hotel.
So if Check IN = 1/22/2012 and Check Out = 1/27/2011 then Room Nights = 5
I need to append this into the table "Hotel Stays Days For Each Day" from table "Hotel Stay Days"
All fields append to same fields that are in the Each Day table. The fields not needed do not append.
Check In Date = Stay Date ( I need all the check in dates for each record to go into stay date)
I am not sure how to code the rest.
If a record has 5 nights as noted above then need keep appending for each night stayed but the first Check in date = first stay date
the next stay date = Check In Date +1 ( the count for while needs to start at Room nights -1)
we only need 4 appends for this example.
then the next thing is tricky. I
f the 2nd stay = Check in Date +1 while Count = 4 (5-1)
then the 3rd stay = Check in Date +2 while Count = 3 (4-1)
then 4th stay = Check in Date +3 while count = 2 (3-1)
then the 5th Stay = Check in Date + 4 while count = 1 (2-1)
All new dates with adding going into Stay Date.
I am think a While Statement can be used?
The trick is to use the Room Nights as the counting mechanism to adding the appends needed.
I think like a reverse count to append?
If start at 5 Room Nights and count = Room nights -1
then we can add Current count = 4 (5-1)
then we can add Current Count = 3 (4-1)
then we can add Current Count = 2 (3-1)
then we can Add Current count = 1 (2-1)
then we can add Current Count = 0 (1-1)
We can then know at end of last append working backwards that we have each stay day included. When count then = -1 then stop.
I hope this makes sense as not a programmer or coder.
I have attached the file.