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

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

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.

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 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.

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 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.

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

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.

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 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.

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

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.

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

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.

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