We help IT Professionals succeed at work.

Create an Append Code based on While Statement for Hotel records in Access 2007

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.
Hotel-Day-Stay.zip
Comment
Watch Question

Commented:
So if I understand you correctly you have two tables with the same structure, but in the first table you have a range of days specified by check in date and check out date and in the second table you want to enter a single record for each day, so if they stayed 5 days (one record in the first table) you want to create 5 records in the second table.

Is that it?
Test sample. You should not not store similar data in different tables if you add to your main table ID field (it was added).
See qryDays. Sub N_fill could be used if anybody will stay in hotel more then 1000 days.

Hotel-Day-Stay.mdb

Author

Commented:
YES THAT IS IDEA
kmslogic:
 
So if I understand you correctly you have two tables with the same structure, but in the first table you have a range of days specified by check in date and check out date and in the second table you want to enter a single record for each day, so if they stayed 5 days (one record in the first table) you want to create 5 records in the second table.

Is that it?

Author

Commented:
Dear als315:
 This does work! only issue was that the where is a < not <=
as the Checkout date cannot be counted as stay day as the person leaves that day. I changed to  <
Checkout date
I did have main id. RecordKey did add it so got that.

So is the MODULE you created being called when I run the query? it knows to keep adding a date while the count is less by 1 for each time a date is added?
I agree better not to create 2nd table . Had know idea how to do that?
How does your query know what to do with the Numbers table? No link?

I just want to understand how the logic works!!!
Test sample. You should not not store similar data in different tables if Iyou add to your main table ID field (it was added).
See qryDays. Sub N_fill could be used if anybody will stay in hotel more then 1000 days.
 
Idea of query - add days to "Checkin Date" (number of days I take from table Numbers) and remove all dates < Checkout Date (In my example <=). No link to table Numbers means - all possible combinations of two tables.
Function in module is used only once when you are filling table Numbers with sequental numbers. I've filled up to 999. If you need more days, call this sub from some button or from Immediate window with parameter (maximum possible days quantity).
I don't know how do you like to use results of this query in future, but you can:
1. Don't save results in table if query will work quickly enough.
2. Save only RecordID and Day, then take data in query from this and main table (join by RecordID).

Author

Commented:
VEry well done and answered any questions regarding the logic.