If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using dates and room numbers in hotel database
Hi
Im 1 month old to access. I have a list of room numbers say 1-10 and type of bed in a table called rooms Then I have another table called check in which requires the checkin date the ceck out date and the room number. How could I go about having only available rooms between these dates show up in a drop down list? A webpage or advice would be greatly appreciated. I have been to create a dropdown list. Would a query be the way to go? Does what Im describing require VBA knowledge Thanks Mike |
#2
|
|||
|
|||
Using dates and room numbers in hotel database
Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum date spread. RoomDates -- SELECT Rooms.Room, Rooms.Bed, DateAdd("d",[CountNUM],Date()) AS AvailDate FROM Rooms, CountNumber WHERE (((DateAdd("d",[CountNUM],Date()))=Date()+14)); RoomDatesOccup -- SELECT CheckIn.Room, DateAdd("d",[CountNUM],[CheckIn]) AS OccupStart, CheckIn.CheckOut FROM CheckIn, CountNumber WHERE (((DateAdd("d",[CountNUM],[CheckIn]))=[CheckOut]) AND ((CheckIn.CheckOut)=Date())) ORDER BY CheckIn.Room, DateAdd("d",[CountNUM],[CheckIn]); RoomDatesAvail --- SELECT RoomDates.AvailDate, RoomDates.Room, RoomDates.Bed FROM RoomDates LEFT JOIN RoomDatesOccup ON (RoomDates.Room = RoomDatesOccup.Room) AND (RoomDates.[AvailDate] = RoomDatesOccup.[OccupStart]) WHERE (((RoomDatesOccup.Room) Is Null)) ORDER BY RoomDates.AvailDate, RoomDates.Room; -- KARL DEWEY Build a little - Test a little "Mike" wrote: Hi Im 1 month old to access. I have a list of room numbers say 1-10 and type of bed in a table called rooms Then I have another table called check in which requires the checkin date the ceck out date and the room number. How could I go about having only available rooms between these dates show up in a drop down list? A webpage or advice would be greatly appreciated. I have been to create a dropdown list. Would a query be the way to go? Does what Im describing require VBA knowledge Thanks Mike |
#3
|
|||
|
|||
Using dates and room numbers in hotel database
Mike:
Firstly create a table of all dates over a range, say the next 10 years. You can do this easily by serially filling down a column in Excel with dates and then importing it into Access as a table. Call the table Calendar and its one column (field) calDate, which should of course be a date/time data type and the table's primary key. On your data entry form add a combo box, cboRoom, to list the available rooms. Set its ControlSource property to [Room Number]. For its RowSource property use a query which, assuming you want to list the rooms vacant on ALL dates in the range would be: PARAMETERS Forms![YourFormName]![Check In] DATETIME, Forms![YourFormName]![Check Out] DATETIME; SELECT [Room Number] FROM Rooms WHERE NOT EXISTS (SELECT * FROM [Check In], Calendar WHERE CalDate BETWEEN Forms![YourFormName]![Check In] AND Forms![YourFormName]![Check Out] AND CalDate = [Check In] AND CalDate = [Check Out] AND [Check In].[Room Number] = Rooms.[Room Number]) ORDER BY [Room Number]; You'll need to substitute the real name of your form of course and make sure that the table and column names in the query exactly match your own. If you are not familiar with creating a query in SQL then what you do is open the query designer in the usual way; don't add any tables; from the View menu select SQL View; type or paste in the SQL as above, and amend it to use your real form, table and column names. In the AfterUpdate event procedures of both the check in and check out controls on your form put the following code: If Not IsNull(Me.[Check in]) And Not IsNull(Me.[Check Out]) Then Me.cboRoom = Null Me.cboRoom.Requery End If To do this select the control in form design view and open its properties sheet if its not already open. Then select the AfterUpdate event property in the properties sheet. Click on the 'build' button; that's the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA window will open at the event procedure with the first and last lines already in place. Enter the lines of code between these two existing lines. When dates are entered in the check in and check out controls the combo box will be requeried to show only those rooms vacant on ALL dates in the range defined by the check in and check out dates. Ken Sheridan Stafford, England On Feb 15, 9:33 pm, Mike wrote: Hi Im 1 month old to access. I have a list of room numbers say 1-10 and type of bed in a table called rooms Then I have another table called check in which requires the Check In date the ceck out date and the room number. How could I go about having only available rooms between these dates show up in a drop down list? A webpage or advice would be greatly appreciated. I have been to create a dropdown list. Would a query be the way to go? Does what Im describing require VBA knowledge Thanks Mike |
Thread Tools | |
Display Modes | |
|
|