Project Description:
The main portion of the resort is the hotel. The hotel wants to store information about hotel guests, reservations, and rooms. You will design tables, import data from Access and Excel, and create relationships. Then you will be able to create queries and reports from the data.
Instructions: For the purpose of grading the project you are required to perform the following tasks:
1. Start Access. Open the downloaded file named a01_grader_h1.accdb. Save the file with the name a01_grader_h1_LastFirst, replacing LastFirst with your name. In the Security Warning bar, click Enable Content.
2. Import a table from the downloaded Access database a01_grader_h1_Guest.accdb selecting the tblGuests table. Do not save the import steps.
3. Open tblGuests. In record 25, change GuestLastName to Student and GuestFirstName to Access. Close the table.
4. Create a new table in Design view. This table will store reservations. Add the following fields, data types, and descriptions, in this order. Change field sizes as noted.
Field Name Data Type Description Field Size/Format
ReservationID AutoNumber A unique identifier for the reservation Long Integer
GuestID Number The guest id from tblGuests (foreign key) Long Integer
RoomNumber Short Text 30
CheckInDate Date/Time Short Date
NightsStay Number Integer
NumberOfGuests Number Integer
Assign ReservationID as primary key. Save the new table as tblReservations. Close the table.
5. Import the downloaded file a01_grader_h1_Reservations.xlsx from Excel, using the tblReservations worksheet and appending it to tblReservations. The Excel column headers match the Access field names so you can use them. Do not save the import steps.
6. Use the Form tool to create a form for tblReservations, and then save the form as frmReservations_aStudent.
7. Enter the following data into the append record in frmReservations_aStudent. GuestID = 25, RoomNumber = 105, CheckInDate = 4/20/2015, NightsStay = 8, and NumberOfGuests = 1. Close the form.
8. Create a new table in Design view. This table will store information about the hotel rooms. Add the following fields, data types and field sizes in this order:
Field Name Data Type Field Size
RoomNumber Short Text 30
RoomType Short Text 20
Assign RoomNumber as the primary key. Save the new table as tblRooms. Close the table.
9. Import the hotel rooms from the downloaded text file a01_grader_h1_Rooms.csv, appending it to tblRooms. Ensure that Delimited, Comma, and First Row Contains Field Names are selected. Do not save the import steps.
10. Open the Relationships window. Add tblGuests, tblReservations, and tblRooms. Create a one-tomany relationship between GuestID in tblGuests and GuestID in tblReservations. Enforce referential integrity. Do not cascade update or cascade delete.
11. Create a one-to-many relationship between RoomNumber in tblRooms and RoomNumber in tblReservations. Enforce referential integrity. Do not cascade update or cascade delete. Save the changes.
12 Create a relationship report, saving it with the report name Relationships for a01_grader_h1. Close the report and close the Relationships window.
13. Use the Simple Query Wizard to create a query. The query results should list GuestID, GuestFirstName, GuestMiddleInital, GuestLastName, CheckInDate, NightsStay, and RoomType (in that order). Save your query as qryMyRservations_aStudent and then run the query.
14. In Design view, enter criteria to select the guest with GuestID = 25. Sort in ascending order by CheckInDate. Run and then save the query. Close the query.
15. Create a report using the Report Wizard. Add ReservationID, CheckInDate, NightsStay, and RoomType (in that order). View by tblRooms. Sort by CheckInDate and ReservationID in ascending order (in that order). Accept all other defaults. Name your report
rptReservations_aStudent and then finish the wizard.
16 View the report in Layout view. Modify the report title to be Reservations Report by A Student. Save and then close the report.
17 Close the database, and submit as directed.
Attachment:- Assignment.rar