Home

Example Database: The Ceil Inn Hotel

   

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 Primary Key
  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:
     
    Ceil Inn - Employees
  16. Save the form and switch it to Form View
     
    Ceil Inn - Employees
  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 Primary Key
  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 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 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:
     
    Ceil Inn - Room Types
  16. Save the form and switch it to Form View
     
    Ceil Inn - Room Types
  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:
     
    Ceil Inn - Bed Types
  16. Save the form and switch it to Form View
     
    Ceil Inn - Bed Types
  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:
     
    Ceil Inn - Room Status
  16. Save the form and switch it to Form View
     
    Ceil Inn - Room Status
  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:
     
    Rooms
  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:

Ceil Inn - 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
     
    Ceil Inn - Available Rooms
  11. Close the Query Builder
  12. When asked whether you want to save, click Yes
  13. Design the form approximately as follows:
     
    Ceil Inn - Available Rooms
    Control Name Caption Back Color Control Source
    Label Label   Available Rooms    
    Line Line        
    Label Label   Room #    
    Label Label   Room Type    
    Label Label   Bed Type    
    Label Label   Rate    
    Text Box Text Box RoomNumber     RoomNumber
    Text Box Text Box RoomType     RoomType
    Text Box Text Box BedType     BedType
    Text Box 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 Run
  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:
     
    Ceil Inn - Rooms Occupancies
    Control Name Caption Back Color Control Source
    Label Label   Ceil Inn: Payment    
    Label Label   Receipt #:    
    Text Box Text Box ReceiptNumber     ReceiptNumber
    Label Label   Payment Date:    
    Text Box Text Box PaymentDate     PaymentDate
    Label Label   Processed By Text Light  
    Label Label   Employee #:    
    Text Box Text Box EmployeeNumber     EmployeeNumber
    Text Box Text Box txtEmployeeName     =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'"))
    Label Label   Occupyer Information Text Light  
    Label Label   Customer Acnt #:    
    Text Box Text Box AccountNumber     AccountNumber
    Label Label   Phone #:    
    Label Label   Customer Name:    
    Text Box Text Box txtCustomerName     =IIf(IsNull([AccountNumber]),"",DLookUp("LastName","Customers","AccountNumber = '" & [AccountNumber] & "'")+', '+DLookUp("FirstName","Customers","AccountNumber = '" & [AccountNumber] & "'"))
    Text Box Text Box txtPhoneNumber     =IIf(IsNull([AccountNumber]),"",DLookUp("PhoneNumber","Customers","AccountNumber = '" & [AccountNumber] & "'"))
    Label Label   Room Information Text Light  
    Label Label   Date Occupied    
    Text Box Text Box DateOccupied     DateOccupied
    Label Label   Room #:    
    Text Box Text Box RoomNumber     RoomNumber
    Label Label   Room Type:    
    Text Box Text Box txtRoomType     =IIf(IsNull([RoomNumber]),"",DLookUp("RoomType","Rooms","RoomNumber = '" & [RoomNumber] & "'"))
    Label Label   Bed Type:    
    Text Box Text Box txtBedType     =IIf(IsNull([RoomNumber]),"",DLookUp("BedType","Rooms","RoomNumber = '" & [RoomNumber] & "'"))
    Label Label   Regular Rate:    
    Text Box Text Box txtRegularRate     =IIf(IsNull([RoomNumber]),"",DLookUp("Rate","Rooms","RoomNumber = '" & [RoomNumber] & "'"))
    Label Label   Rate Applied:    
    Text Box Text Box RateApplied     RateApplied
    Label Label   Notes:    
    Text Box Text Box Notes     Notes
  30. Using the Button control Button 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
     
    Ceil Inn - Rooms Occupancies
  31. Save the form and switch it to Form View
     
    Ceil Inn - Rooms Occupancies
  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:
     
    Ceil Inn - New Room Occupancy
  40. Save the form and switch it to Form View
     
    Ceil Inn - New Room Occupancy
  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:
     
    Ceil Inn - Customers Rooms Occupancies
    Control Name Caption Control Source
    Label Label   Date Occupied  
    Label Label   Room #  
    Label Label   Rate  
    Label Label   Phone Use  
    Text Box Text Box DateOccupied   DateOccupied
    Text Box Text Box RoomNumber   RoomNumber
    Text Box Text Box RateApplied   RateApplied
    Text Box Text Box PhoneCharge   PhoneCharge
    Text Box Text Box txtRateTotal   =Sum([RateApplied])
    Text Box 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 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):
     
    Ceil Inn - Customers
    Control Name Caption Back Color Control Source
    Label Label   Room Use:    
    Text Box Text Box txtRoomUse     =[sbfOccupancies].[Form]![txtRateTotal]
    Label Label   Phone Use:    
    Text Box Text Box txtPhoneUse     =[sbfOccupancies].[Form]![txtPhoneUse]
    Label Label   Total:    
    Text Box Text Box txtTotal     =Nz([txtRoomUse])+Nz([txtPhoneUse])
  62. Save and switch it to Form View
     
    Ceil Inn - Customers
  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 Run
  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:
     
    Ceil Inn - Rooms Payments
    Control Name Caption Back Color Control Source
    Label Label   Ceil Inn: Payment    
    Label Label   Receipt #:    
    Text Box Text Box ReceiptNumber     ReceiptNumber
    Label Label   Payment Date:    
    Text Box Text Box PaymentDate     PaymentDate
    Label Label   Processed By Text Light  
    Label Label   Employee #:    
    Text Box Text Box EmployeeNumber     EmployeeNumber
    Text Box Text Box txtEmployeeName     =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'"))
    Label Label   Occupyer Information Text Light  
    Label Label   Customer Acnt #:    
    Text Box Text Box AccountNumber     AccountNumber
    Label Label   Phone #:    
    Label Label   Customer Name:    
    Text Box Text Box txtCustomerName     =IIf(IsNull([AccountNumber]),"",DLookUp("LastName","Customers","AccountNumber = '" & [AccountNumber] & "'")+', '+DLookUp("FirstName","Customers","AccountNumber = '" & [AccountNumber] & "'"))
    Text Box Text Box txtPhoneNumber     =IIf(IsNull([AccountNumber]),"",DLookUp("PhoneNumber","Customers","AccountNumber = '" & [AccountNumber] & "'"))
    Label Label   Room Occupied From:    
    Label Label   Amount Charged:    
    Text Box Text Box AmountCharged     AmountCharged
    Label Label   Tax Rate:    
    Text Box Text Box TaxRate     TaxRate
    Label Label   Tax Amount:    
    Text Box Text Box txtTaxAmount      
    Label Label   Amount Paid:    
    Text Box Text Box txtPaymentTotal      
    Label Label   Notes:    
    Text Box Text Box Notes     Notes
  16. Save the form and switch it to Form View
     
    Ceil Inn - Rooms Payments
  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:
     
    Ceil Inn - New Room Payment
  25. Save the form and switch it to Form View
     
    Ceil Inn - New Room Payment
  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:
    Ceil Inn - New Room Payment
  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
     
    Ceil Inn - Customers
  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-2012 FunctionX Home