Introduction

Ceil Inn is a (fictional) hotel. The company provides various types rooms (bedrooms, suites, and conference rooms). Rooms have different types of bed (queen, double, or king). Depending on the type of room and the bed(s), a certain rate is charged to occupy or use a room.

We are asked to create a database application that would help the employees of Ceil Inn to manage their business.

Practical Learning: Starting the Application

1. Start Microsoft Access
2. In the opening window, change the File Name to CeilInn1
3. Click Create
4. Click File and click Options
5. Click Current Database
6. Click Overlapping Windows
7. Click Compact On Close
8. Click Remove Personal Information From File Properties On Save
9. Click OK
10. Click OK.
    If you want, close Microsoft Access (you don't really have to, but the screen shots in this lesson will use overlapping windows), then re-start it and open the CeilInn1 database

The Employees of the Company

Employees are people who will manage all aspects of the database. In most databases, they are identified with as much information as possible. To keep our implication simple, we will need the name and the title of each employee. To uniquely identity an employee, each one will have an employee number. This number will be specified but the person who is creating the record for a new hire.

Practical Learning: Creating the Employees

1. On the Ribbon, click Create
2. To create a new table, in the Tables section, click the Table Design
3. Click under Field Type and type EmployeeNumber
4. Press Tab and accept its Data Type as Text.
   While the field is still selected, in the Tools section of the Ribbon, click the Primary Key button
5. In the lower section of the window, set the Field Size to 20
6. In the top section of the window, click under EmployeeNumber
7. Complete the list of fields as follows:
    

   Field Name	Data Type	Caption	Field Size
   EmployeeNumber	Text	Employee #	20
   FirstName	Text	First Name	25
   LastName	Text	Last Name	25
   Title	Text		50
   Notes	Memo

8. To close and save the table, right-click Table1 and click Close
9. When asked whether you want to save the table, click Yes
10. Set the name to Employees and
11. Click OK
12. To create a form for the room types table, on the Ribbon, click Create and, in the Forms section, click Form Design
13. Save the form as Employees
14. Using the Properties window, set its Record Source as Employees
15. Design the form approximately as follows:
     
    
16. Save the form and switch it to Form View
     
    
17. Close the form

The Customers of the Hotel

The customers are the people who rent the rooms that the hotel offers. Like employees, customers have to be identified. For our database, we will just need each customer's name, the telephone number, and information about a person to contact in case of emergency.

When a customer comes to rent a room, an account will be created for him or her. If the same customer comes to rent a room another time, a new accuont will be created. This means that, for accounting purposes, we will need a unique account number for each renting session, even for a repeating customer.

Practical Learning: Creating the Customers

1. On the Ribbon, click Create
2. To create a new table, in the Tables section, click the Table Design
3. Click under Field Type and type AccountNumber
4. Press Tab and accept its Data Type as Text.
   While the field is still selected, in the Tools section of the Ribbon, click the Primary Key button
5. In the lower section of the window, set the Field Size to 20
6. In the top section of the window, click under AccountNumber
7. Complete the list of fields as follows:
    

   Field Name	Data Type	Caption	Field Size
   AccountNumber	Text	Account Number	20
   FirstName	Text	First Name	25
   LastName	Text	Last Name	25
   PhoneNumber	Text	Phone Number	20
   EmergencyName	Text	Emergency Name	50
   EmergencyPhone	Text	Emergency Phone	20
   Notes	Memo

8. To close and save the table, right-click its title bar and click Close
9. When asked whether you want to save the table, click Yes
10. Set the name to Customers and
11. Click OK

The Types of Rooms

 The Ceil Inn hotel offers different categories of rooms. A regular bedroom is equipped with one or two beds, one table, one sofa or recliner, two chairs, a closet, and an iron. Besides being a place to sleep, the room has a telephone (analogue line), a kitchen area that has a stove, a refrigerator, a dish washer, and cabinets. The cabinets contain spoons, forks, knives, and plates.

For our database, we will need a table named Room Types.

Practical Learning: Creating the Types of Rooms

1. On the Ribbon, click Create
2. To create a new table, in the Tables section, click the Table Design
3. Click under Field Type and type RoomType
4. Press Tab and accept its Data Type as Text.
   While the field is still selected, in the Tools section of the Ribbon, click the Primary Key button
5. In the lower section of the window, set the Field Size to 25
6. In the top section of the window, click under RoomType and type Notes
7. Press Tab and type m and make sure Memo is selected
8. To close and save the table, double-click the Table1 icon
9. When asked whether you want to save the table, click Yes
10. Set the name to RoomTypes and
11. Click OK
12. To create a form for the room types table, on the Ribbon, click Create and, in the Forms section, click Form Design
13. Save the form as RoomTypes
14. Using the Properties window, set its Record Source as RoomTypes
15. Design the form approximately as follows:
     
    
16. Save the form and switch it to Form View
     
    
17. Close the form

The Types of Beds

Most of the business of Ceil Inn hotel is based on rooms the customers rent, and most customer rent a room to spend the night. To serve such customers, bedroom have different types of bed. The most common bed has a queen size that can serve one or two people. Another category of room uses a king size, which is larger and wider than the queen. Some customers wants a single room but with different beds. Such rooms have a double bed. Of course, conference rooms do not have a bed.

Practical Learning: Creating the Types of Beds

1. On the Ribbon, click Create
2. To create a new table, in the Tables section, click the Table Design
3. Click under Field Type and type BedType and press Tab
4. Right-click BedType and click Primary Key
5. In the lower section of the window, set the Field Size to 25
6. In the top section of the window, click under BedType and type Notes
7. Press Tab and type m and make sure Memo is selected
8. To close and save the table, right-click Table1 and click Close
9. When asked whether you want to save the table, click Yes
10. Set the name to BedTypes and
11. Click OK
12. To create a form, on the Ribbon, click Create and, in the Forms section, click Form Design
13. Save the form as BedTypes
14. Using the Properties window, set its Record Source as BedTypes
15. Design the form approximately as follows:
     
    
16. Save the form and switch it to Form View
     
    
17. Close the form

The Occupancy Status of a Room

The hoteil has different rooms that are rented or freed some time to time. When a room is not rented, it has the status of available. When a room has been rented, its occupancy status must be changed to occupied. To take care of this, we will create a table for rooms status.

This is the idea behind a relational database. A relational database is an application that contains two or more tables so that information in one table is made available to another table or other tables that need(s) it. The information is entered once in one particular table. If the same information is needed in another table, it is simply identified one way or another. This reduces, and can eliminate, the likelihood of mistakes that result from duplicate data.

Practical Learning: Creating the Occupancies Status of Rooms

1. On the Ribbon, click Create
2. To create a new table, in the Tables section, click the Table Design
3. Click under Field Type and type RoomStatus and press Tab
4. Right-click BedType and click Primary Key
5. In the lower section of the window, set the Field Size to 30
6. In the top section of the window, click under BedType and type Notes
7. Press Tab and type m and make sure Memo is selected
8. To close and save the table, right-click Table1 and click Close
9. When asked whether you want to save the table, click Yes
10. Set the name to RoomsStatus and
11. Click OK
12. To create a form, on the Ribbon, click Create and, in the Forms section, click Form Design
13. Save the form as RoomsStatus
14. Using the Properties window, set its Record Source as RoomsStatus
15. Design the form approximately as follows:
     
    
16. Save the form and switch it to Form View
     
    
17. Close the form

Hotel Rooms

Rooms are probably the most important objects of a hotel. A room is primarily characterized by its category as a bedroom or a conference room. If it's a bedroom, then other pieces of information are necessary. We have already seen what they are: bed type and status.

Each room must have a unique room number. The rate applied when renting a room should be specified. After all, en employee should not guess the price of a room when a customer is about to rent it.

Practical Learning: Creating the Rooms 

1. On the Ribbon, click Create. In the Tables section, click the Table Design
2. Set the name of the first field to RoomNumber and press Tab
3. Right-click RoomNumber and click Primary Key
4. To save the table, right-click Table1 and click Save
5. Set the name to Rooms and click Save
6. Click under RoomNumber
7. Type RoomType and press Tab
8. Press L and press Tab
9. In the first page of the wizard, make sure the first radio button is selected and click Next
10. In the list of tables, click RoomTypes and click Next
11. Double-click RoomType and click Next
12. Click Next
13. Click Finish
14. When asked to save the table, click Yes
15. Click under RoomType
16. Type BedType and press Tab
17. Type L and press Tab
18. In the first page of the wizard, make sure the first radio button is selected and click Next
19. In the list of tables, click BedTypes and click Next
20. Double-click BedType and click Next
21. Click Next
22. Click Finish
23. When asked to save the table, click Yes
24. Click under BedType and type Rate
25. Click Rate and type RoomStatus
26. In the corresponding Data Type, select Lookup Wizard
27. In the first page of the wizard, make sure the first radio button is selected and click Next
28. In the list of tables, click RoomsStatus and click Next
29. Double-click RoomStatus and click Next
30. Click Next
31. Click Finish
32. When asked to save the table, click Yes
33. Complete the table as follows:
     

    Field Name	Data Type	Caption	Field Size	Format
    RoomNumber	Text	Room Number	10
    RoomType	Text	Room Type
    BedType	Text	Bed Type
    Rate	Number		Double	Fixed
    RoomStatus	Text	Status
    Notes	Memo

34. Save the table
35. Close it
36. To create a form for the rooms table, on the Ribbon, click Create
37. In the Forms section, click Form Design
38. Save the form as Rooms
39. In the Properties window, set the Record Source to Rooms
40. Design the form approximately as follows:
     
    
41. Save the form

Rooms Occupancies

After registering a customer, the employee can assign a room. Normally, th employee would ask what type of room the customer wants to rent. After all some people want a bedroom, others a studio, and others a conference room. The employee can (create or) use a query that shows only the available rooms:

Probably a better way is to create a form that would show the list of available rooms.

When assigning a room to a customer, we first need to know who (the employee) performed this operation. We also would like to know the date when this operation was performed. We may just use that same date as the first day a customer rented a room or the day a conference room was used. Of course, the employee needs to identify the customer who is renting the room. The employee must also specify the room that is being rented and how much will be charged. To know the rate, the employee can use the same query or form mentioned previously for available rooms.

In our hotel, the Internet is free to all customers (we remember when hotels used to charge for Internet use; most of them now offer free Internet and many of them have computers somewhere at the receiption or in some room for customers use). If using the telephone in the room, if a customer makes local calls, they are free. Long distance calls are not free. We need to keep, on a daily basis, a calendar and rate of phone consumption (this is one of the reasons we create a new account for every customer, including returning customer).

As you can imagine, each room assignment must have a unique number that identifies its record. We will use an automatically incrementing number. To make it a little fancy, we will start the numbering at 100001 and increment by 1 for each subsequent record.

Practical Learning: Assigning a Room

1. On the Ribbon, click Create
2. In the Forms section, click Form Design
3. Using the Properties window, change the following characteristics of the form
   Default View: Continuous Forms
   Record Selector: No
   Navigation Buttons: No
4. Save the form as AvailableRooms
5. In the Properties window, click Record Source and click its ellipsis button
6. In the Show Table dialog box, click Rooms
7. Click Add and click Close
8. In the list of fields, double-click RoomNumber, RoomType, BedType, Rate, and RoomStatus
9. In the lower section of the window, click the box at the intersection of RoomStatus and Criteria
10. Type Available
     
    
11. Close the Query Builder
12. When asked whether you want to save, click Yes
13. Design the form approximately as follows:
     
    

    Control Name Caption Back Color Control Source Label   Available Rooms     Line         Label   Room #     Label   Room Type     Label   Bed Type     Label   Rate     Text Box RoomNumber     RoomNumber Text Box RoomType     RoomType Text Box BedType     BedType Text Box Rate     Rate

14. Save and close the form
15. On the Ribbon, click Create and, in the Queries section, click Query Design
16. In the Show Table dialog box, click Close
17. Right-click the top section of the window and click SQL View
18. Type the following code:

    CREATE TABLE Occupancies
    (
        OccupancyNumber Counter(100001, 1) not null
    );

19. To exexcute, in the Results section of the Ribbon, click the Run button
20. Close the Query window
21. When asked whether you want to save, click No
22. In the Navigation Pane, right-click Payments and click Design View
23. Right-click ReceiptNumber and click Primary Key
24. Complete the table as follows:
     

    Field Name	Data Type	Caption	Field Size	Format
    OccupancyNumber		Occupancy Number
    EmployeeNumber	Text	Processed By	20
    DateOccupied	Date/Time	Date Occupied		Long Date
    AccountNumber	Text	Processed For	20
    RoomNumber	Text	Room #	10
    RateApplied	Number	Rate Applied	Double	Fixed
    PhoneCharge	Number	Phone Charge	Double	Fixed
    Notes	Memo

25. Close and save the table
26. To create a form, on the Ribbon, click Create and, in the Forms section, click Form Design
27. Save the form as Occupancies
28. Using the Properties window, set its Record Source as Occupancies
29. Design the form approximately as follows:
     
    

    Control Name Caption Back Color Control Source Label   Ceil Inn: Payment     Label   Receipt #:     Text Box ReceiptNumber     ReceiptNumber Label   Payment Date:     Text Box PaymentDate     PaymentDate Label   Processed By Text Light   Label   Employee #:     Text Box EmployeeNumber     EmployeeNumber Text Box txtEmployeeName     =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'")) Label   Occupyer Information Text Light   Label   Customer Acnt #:     Text Box AccountNumber     AccountNumber Label   Phone #:     Label   Customer Name:     Text Box txtCustomerName     =IIf(IsNull([AccountNumber]),"",DLookUp("LastName","Customers","AccountNumber = '" & [AccountNumber] & "'")+', '+DLookUp("FirstName","Customers","AccountNumber = '" & [AccountNumber] & "'")) Text Box txtPhoneNumber     =IIf(IsNull([AccountNumber]),"",DLookUp("PhoneNumber","Customers","AccountNumber = '" & [AccountNumber] & "'")) Label   Room Information Text Light   Label   Date Occupied     Text Box DateOccupied     DateOccupied Label   Room #:     Text Box RoomNumber     RoomNumber Label   Room Type:     Text Box txtRoomType     =IIf(IsNull([RoomNumber]),"",DLookUp("RoomType","Rooms","RoomNumber = '" & [RoomNumber] & "'")) Label   Bed Type:     Text Box txtBedType     =IIf(IsNull([RoomNumber]),"",DLookUp("BedType","Rooms","RoomNumber = '" & [RoomNumber] & "'")) Label   Regular Rate:     Text Box txtRegularRate     =IIf(IsNull([RoomNumber]),"",DLookUp("Rate","Rooms","RoomNumber = '" & [RoomNumber] & "'")) Label   Rate Applied:     Text Box RateApplied     RateApplied Label   Notes:     Text Box Notes     Notes

30. Using the Button control in the Ribbon, add a button to the Footer section of the form. In the first page of the wizard, select Form Operations and Open Form then click Next. In the second page of the wizard, click AvailableRooms and click Finish
     
    
31. Save the form and switch it to Form View
     
    
32. Close the form
33. To create another form, in the Navigation Pane, right-click Occupancies and click Copy
34. Right-click any section in the Navigation Pane and click Paste
35. Type the name as NewOccupancy
36. Click OK
37. In the Navigation Pane, right-click NewOccupancy and click Design New
38. Using the Properties window, change the following characteristics of the form:
    Modal: Yes
    Auto Center: Yes
    Border Style: Dialog
    Navigation Buttons: No
    Data Entry: Yes
39. Change the design of the form as follows:
     
    
40. Save the form and switch it to Form View
     
    
41. Close the form
42. On the Ribbon, click Create
43. In the Forms section, click Form Design
44. Right-click the body of the form and click Form Header/Footer
45. Save the form as sfOccupancies
46. Using the Properties window, change the following characteristics of the form
    Default View: Continuous Forms
    Record Selector: No
    Navigation Buttons: No
47. In the Properties window, set the Record Source to Occupancies
48. Click the Form Footer bar and, in the Properties, set its Visible field to No
49. Design the form approximately as follows:
     
    

    Control Name Caption Control Source Label   Date Occupied   Label   Room #   Label   Rate   Label   Phone Use   Text Box DateOccupied   DateOccupied Text Box RoomNumber   RoomNumber Text Box RateApplied   RateApplied Text Box PhoneCharge   PhoneCharge Text Box txtRateTotal   =Sum([RateApplied]) Text Box txtPhoneUse   =Sum([PhoneCharge])

50. Save and close the sub-form
51. On the Ribbon, click Create
52. In the Forms section, click Form Design
53. In the Properties window, change the Record Source to Customers
54. Save the form as Customers
55. In the Controls section of the Ribbon, click Subform/Subreport and click the body of the form
56. In the first page of the wizard, click the Use An Existing Form radio button and click sfOccupancies
57. Click Next
58. In the second page of the wizard, make sure Show Occupancies For Each Record In Customers Using AccountNumber is selected.
    Click Next
59. Type Room Occupany Summary
60. Click Finish
61. Design the form approximately as follows (we will list only three new text boxes you should add to the form and whose Control Source doesn't depend directly on the Customers table):
     
    

    Control Name Caption Back Color Control Source Label   Room Use:     Text Box txtRoomUse     =[sbfOccupancies].[Form]![txtRateTotal] Label   Phone Use:     Text Box txtPhoneUse     =[sbfOccupancies].[Form]![txtPhoneUse] Label   Total:     Text Box txtTotal     =Nz([txtRoomUse])+Nz([txtPhoneUse])

62. Save and switch it to Form View
     
    
63. Close the form

Payments

A conference room is usually rented for one day or one evening. A bedroom is usually rented for one or more nights. If a room (whether a conference room or a bedroom) is rented for one day or night, the day will be registed. The rate will be applied. At the end of the day, the customer can pay. If a room is rented for one whole night (the classic case of a bedroom), the employee will register the first day but no rate will be applied. Each subsequent day will be registered and a rate will be applied for that day. After the number of days the customer would have used the room, the number of days will be counted and the sum of daily rates made. The customer can then pay the total.

We will need or use the following pieces of information for each payment:

• Receipt Number: A receipt should be given to a customer
• Employee Number: This is the employee who processed the payment. This is usually the employee at the desk when the customer checked out
• Payment Date: This is the date the payment was made. It may not be the checked out date (a customer could pay on the check-iin date or a few days after check-out)
• Account Number: As mentioned previously, when a customer registers with the hotel, (s)he gets an account number. While the customer is using the room, this account number is used to keep track of both the customer's occupancy and phone use. This the reason we decide to create a new account every time, including a returning customer. When the customer decides to pay the bill, the account number holds a summary of his or her consumption
• Amount Charged: This is the total resulting from the customer renting a conference room or occupying the bedroom
• Tax Rate: The government needs to collect its due
• Notes: Comments can be made in this field

Practical Learning: Making Payments

1. On the Ribbon, click Create and, in the Queries section, click Query Design
2. In the Show Table dialog box, click Close
3. Right-click the top section of the window and click SQL View
4. Type the following code:

   CREATE TABLE Payments
   (
       ReceiptNumber Counter(1001, 1) not null
   );

5. To exexcute, in the Results section of the Ribbon, click the Run button
6. Close the Query window
7. When asked whether you want to save, click No
8. In the Navigation Pane, right-click Occupancies and click Design View
9. Right-click OccupancyNumber and click Primary Key
10. Complete the table as follows:
     

    Field Name	Data Type	Caption	Field Size	Format	Default Value
    ReceiptNumber		Receipt Number
    EmployeeNumber	Text	Processed By	20
    PaymentDate	Date/Time	Payment Date		Long Date
    AccountNumber	Text	Processed For	20
    AmountCharged	Number	Amount Charged	Double	Fixed
    TaxRate	Number	Tax Rate	Double	Percent	0.0775
    Notes	Memo

11. Close and save the table
12. To create a form, on the Ribbon, click Create and, in the Forms section, click Form Design
13. Save the form as Payments
14. Using the Properties window, set its Record Source as Payments
15. Design the form approximately as follows:
     
    

    Control Name Caption Back Color Control Source Label   Ceil Inn: Payment     Label   Receipt #:     Text Box ReceiptNumber     ReceiptNumber Label   Payment Date:     Text Box PaymentDate     PaymentDate Label   Processed By Text Light   Label   Employee #:     Text Box EmployeeNumber     EmployeeNumber Text Box txtEmployeeName     =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'")) Label   Occupyer Information Text Light   Label   Customer Acnt #:     Text Box AccountNumber     AccountNumber Label   Phone #:     Label   Customer Name:     Text Box txtCustomerName     =IIf(IsNull([AccountNumber]),"",DLookUp("LastName","Customers","AccountNumber = '" & [AccountNumber] & "'")+', '+DLookUp("FirstName","Customers","AccountNumber = '" & [AccountNumber] & "'")) Text Box txtPhoneNumber     =IIf(IsNull([AccountNumber]),"",DLookUp("PhoneNumber","Customers","AccountNumber = '" & [AccountNumber] & "'")) Label   Room Occupied From:     Label   Amount Charged:     Text Box AmountCharged     AmountCharged Label   Tax Rate:     Text Box TaxRate     TaxRate Label   Tax Amount:     Text Box txtTaxAmount       Label   Amount Paid:     Text Box txtPaymentTotal       Label   Notes:     Text Box Notes     Notes

16. Save the form and switch it to Form View
     
    
17. Close the form
18. To create another form, in the Navigation Pane, right-click Payments and click Copy
19. Right-click any section in the Navigation Pane and click Paste
20. Type the name as NewPayment
21. Click OK
22. In the Navigation Pane, right-click NewPayment and click Design New
23. Using the Properties window, change the following characteristics of the form:
    Modal: Yes
    Auto Center: Yes
    Border Style: Dialog
    Navigation Buttons: No
    Data Entry: Yes
24. Change the design of the form as follows:
     
    
25. Save the form and switch it to Form View
     
    
26. Close the form
27. On the Ribbon, click Database Tools and click Relationships
28. In the Show Table dialog box, double-click each table
29. Click Close
30. Configure the relationships as follows:

31. Close the Relationships window
32. When asked whether you want to save, click Yes

Data Entry

Data entry of our hotel application should be classic. As examples to test the tables and form, we will provide sample values.

Practical Learning: Testing the Database

1. In the Navigation Pane, double-click the Customers form
2. Create the following records:
    

   Employee #	First Name	Last Name	Title
   22958	Andrew	Laskin	General Manager
   72947	Sheryl	Shegger	Intern
   27049	Harriett	Dovecott	Accounts Associate
   70429	Lynda	Fore	Shift Manager
   24095	Fred	Barclay	Accounts Associate
   28405	Peggy	Thompson	Accounts Associate

3. Close the form
4. In the Navigation Pane, double-click the Customers form
5. Create the following records:
    

   Account #	First Name	Last Name	Phone Number	Emergency Name	Emergency Phone
   208405	Peter	Carney	990-585-1886	Spencer Miles	990-750-8666
   204795	Juliette	Beckins	410-944-1440	Bernard Brodsky	410-385-2235
   902840	Daniel	Peters	624-802-1686	Grace Peters	877-490-9333
   383084	Peter	Carney	990-585-1886	Spencer Miles	990-750-8666
   660820	Anne	Sandt	953-172-9347	William Sandt	953-279-2475
   100752	Caroline	Lomey	301-652-0700	Albert Lomey	301-412-5055
   608208	Alfred	Owens	804-798-3257	Jane Owens	240-631-1445
   180204	Randy	Whittaker	703-631-1200	Bryan Rattner	703-506-9200
   284085	Lucy	Chen	425-979-7413	Edward Lamb	425-720-9247
   260482	Caroline	Lomey	301-652-0700	Albert Lomey	301-412-5055
   474065	Peter	Carney	990-585-1886	Spencer Miles	990-750-8666
   640800	Randy	Whittaker	703-631-1200	Bryan Rattner	703-506-9200
   294209	Doris	Wilson	703-416-0934	Gabriela Dawson	703-931-1000
   946090	Peter	Carney	990-585-1886	Spencer Miles	990-750-8666
   629305	Joan	Davids	202-789-0500	Rebecca Boiron	202-399-3600
   608502	Caroline	Lomey	301-652-0700	Albert Lomey	301-412-5055

6. Close the form
7. In the Navigation Pane, double-click the RoomTypes form
8. Create the following records:
    

   RoomType

   Bedroom

   Studio

   Conference

9. Close the form
10. In the Navigation Pane, double-click the BedTypes form
11. Create the following records:
     

    BedType

    Queen

    King

    Double

12. Close the form
13. In the Navigation Pane, double-click the RoomsStatus form
14. Create the following records:
     

    RoomStatus

    Available

    Occupied

    Other

15. Close the form
16. In the Navigation Pane, double-click the Rooms form
17. Create the following records:
     

    Room #ID	Room Type	Bed Type	Rate	Room Status
    101	Bedroom	Queen	85.95
    102	Bedroom	Double	92.50	Available
    103	Studio	Double	112.50
    104	Bedroom	Queen	85.95	Available
    105	Bedroom	King	98.75	Available
    106	Bedroom	Queen	85.95	Available
    107	Bedroom	King	98.75	Available
    108	Bedroom	Queen	85.95	Available
    110	Conference		450.00	Available
    112	Conference		650.00	Available
    114	Bedroom	King	98.75	Available
    115	Bedroom	King	98.75	Available
    116	Bedroom	Queen	85.95	Available
    117	Bedroom	Queen	85.95	Available
    118	Bedroom	King	98.75	Available
    120	Studio	King	124.95	Available
    122	Conference		725.00
    125	Bedroom	King	95.50	Available
    126	Studio	King	124.95	Available
    127	Bedroom	Double	92.50	Available
    128	Bedroom	Double	92.50	Available
    130	Conference		500.00	Available
    201	Bedroom	Double	92.50	Available
    202	Studio	King	124.95
    203	Studio	Queen	94.50	Available
    204	Bedroom	Double	96.50	Available
    205	Bedroom	Queen	85.95	Available
    206	Bedroom	King	92.75
    207	Bedroom	Queen	85.95	Available
    208	Bedroom	Queen	85.95	Available
    209	Bedroom	King	92.75	Available
    210	Studio	Double	112.50

18. Close the form
19. In the Navigation Pane, double-click the NewOccupancy form
20. Create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    24095	100752	Monday, January 02, 2012	106			The customer is here for a conference and will occupy the room for a week.
    	100752	Tuesday, January 03, 2012	106	85.95
    	100752	Wednesday, January 04, 2012	106	85.95
    	100752	Thursday, January 05, 2012	106	85.95
    28405	100752	Friday, January 06, 2012	106	85.95

21. Close the Occupancies form
22. Re-open the Customers form to see its first record
     
    
23. Close the Customers form
24. In the Navigation Pane, double-click NewPayment
25. Create a record as follows:
    Payment Date: Friday, January 06, 2012
    Employee #: 28405
    Customer Acnt #: 100752
    Amount Charged: 343.80
26. Close the NewPayment form
27. Open the NewOccupancy form
28. Create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    28405	946090	Saturday, January 07, 2012	114		3.55
    28405	474065	Saturday, January 07, 2012	110	450.00		The room is rented for an on-going conference
    24095	204795	Sunday, January 07, 2012	104
    27049	204795	Saturday, January 08, 2012	104	85.95
    28405	946090	Sunday, January 08, 2012	114	98.95	18.86

29.  Close the NewOccupancy form
30. Open the NewPayment form and create a record as follows:
    Payment Date: Friday, January 08, 2012
    Employee #: 28405
    Customer Acnt #: 946090
    Amount Charged: 98.95
31. Create another record as follows:
    Payment Date: Friday, January 08, 2012
    Employee #: 24095
    Customer Acnt #: 204795
    Amount Charged: 85.95
32. Close the NewPayment form
33. Open the NewOccupancy form and create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    24095	208405	Monday, January 09, 2012	203
    24095	284085	Monday, January 09, 2012	106
    24095	294209	Monday, January 09, 2012	205

34. Close the New Occupancy form
35. Open the NewPayment form and create a record as follows:
    Payment Date: Friday, January 09, 2012
    Employee #: 70429
    Customer Acnt #: 474065
    Amount Charged: 450.00
36. Close the NewPayment form
37. Open the NewOccupancy form and create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    24095	208405	Monday, January 09, 2012	203
    24095	284085	Monday, January 09, 2012	106
    24095	294209	Monday, January 09, 2012	205
    	208405	Tuesday, January 10, 2012	203	94.50		The customer is here for a conference and will occupy the room for a week.
    	284085	Tuesday, January 10, 2012	106	85.95
    	294209	Tuesday, January 10, 2012	205	85.95
    	208405	Wednesday, January 11, 2012	203	94.50	2.25
    	284085	Wednesday, January 11, 2012	106	85.95
    	294209	Wednesday, January 11, 2012	205	85.95
    	208405	Thursday, January 12, 2012	203	94.50
    	284085	Thursday, January 12, 2012	106	85.95	3.15
    	294209	Thursday, January 12, 2012	205	85.95
    	208405	Friday, January 13, 2012	203	94.50	4.05
    	284085	Friday, January 13, 2012	106	85.95	5.52
    	294209	Friday, January 13, 2012	205	85.95

38. Close the NewOccupancy form
39. Open the NewPayment form and create the following records:
     
    

    Processed By	Payment Date	Account #
    28405	Saturday, January 14, 2012	208405	472.50
    28405	Saturday, January 14, 2012	284085	387.92
    24095	Saturday, January 14, 2012	294209	379.25	The customer was charged $300

40. Close the NewPayment form
41. Open the NewOccupancy form and create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    28405	208405	Saturday, January 14, 2012	203	94.50
    70429	383084	Saturday, January 14, 2012	112	650.00	22.64	The conference room is rented for a wedding party
    28405	284085	Saturday, January 14, 2012	106	85.95
    24095	294209	Saturday, January 14, 2012	205	85.95
    28405	902840	Saturday, January 14, 2012	107
    	608502	Saturday, January 14, 2012	120		4.26
    	180204	Saturday, January 14, 2012	126
    	629305	Saturday, January 14, 2012	122	725.00
    28405	660820	Saturday, January 14, 2012	105

42. Close the NewOccupancy form
43. Re-open the NewPayment form and create the following record:
     
    

    Processed By	Payment Date	Account #		Notes
    70429	Saturday, January 14, 2012	383084	670.00	The customer was charged a flat rate of $670 for the conference rom.

44. Close the NewPayment form
45. Open the NewOccupancy form and create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    28405	208405	Saturday, January 14, 2012	203	94.50
    70429	383084	Saturday, January 14, 2012	112	650.00	22.64	The conference room is rented for a wedding party
    28405	284085	Saturday, January 14, 2012	106	85.95
    24095	294209	Saturday, January 14, 2012	205	85.95
    28405	902840	Saturday, January 14, 2012	107
    	608502	Saturday, January 14, 2012	120		4.26
    	180204	Saturday, January 14, 2012	126
    	629305	Saturday, January 14, 2012	122	725.00
    28405	660820	Saturday, January 14, 2012	105

46. Close the NewOccupancy form
47. Re-open the NewPayment form and create the following records:
     
    

    Processed By	Payment Date	Account #
    70429	Sunday, January 15, 2012	260482	450.00

48. Close the NewPayment form
49. Open the NewOccupancy form and create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    27049	902840	Sunday, January 15, 2012	107	85.75
    27049	608502	Sunday, January 15, 2012	120	98.85	8.48
    28405	180204	Sunday, January 15, 2012	126	98.85
    70429	260482	Sunday, January 15, 2012	110	450.00
    	660820	Sunday, January 15, 2012	105	92.75
    28405	660820	Monday, January 16, 2012	105	92.75
    28405	608502	Wednesday, January 18, 2012	114

50. Close the NewOccupancy form
51. Re-open the NewPayment form and create the following record:
     
    

    Processed By	Payment Date	Account #
    28405	Wednesday, January 18, 2012	180204	98.85

52. Close the NewPayment form
53. Open the NewOccupancy form and create the following records:
     

    Employee #	Customer Acnt #:	Date Occupied	Room #	Rate Applied	Phone Charge	Notes
    28405	608502	Wednesday, January 18, 2012	114
    28405	608502	Thursday, January 19, 2012	114	92.75	6.82
    70429	608502	Friday, January 20, 2012	114	92.75
    	640800	Friday, January 20, 2012	204
    	640800	Saturday, January 21, 2012	204	96.60
    27049	640800	Sunday, January 22, 2012	204	96.60

54. Close the NewOccupancy form
55. Re-open the NewPayment form and create the following record:
     
    

    Processed By	Payment Date	Account #
    70429	Friday, January 27, 2012	608208	192.32

56. Close the NewPayment form
57. Re-open the Customers form and navigate through its records
58. Close the Customers form
59. Close Microsoft Access

Home	Copyright © 2010-2021, FunctionX	Home