Start Access. Open the downloaded file named a01_grader_h2_HotelRoom.accdb. Save the file with the name a01_grader_h2_HotelRoom_LastFirst, using your last and first name. If necessary, enable the content.

Create a new table in Design view. This table will store the room types. Add the following fields (in this order): RoomType, and then RoomAmenities. Set the data type for the RoomAmenities field to Long Text and accept the default data type for the RoomType field. Change the field size for the RoomType field to 20. Do not assign a primary key. Save the new table as tblRoomType. Close the table.

The data for this table has been stored in a text file delimited with tabs, a01_grader_h2_Type.txt. Import the data, and append it to tblRoomType. Do not save the import steps.

Add a new field to be the primary key in tblRoomType. The field should be the first field in the table. Enter the field name as RoomTypeID and set the data type to AutoNumber. Make the field the primary key. Save and close the table.

Create a new table in Design view. This table will store individual rooms. Add the following fields (in this order): RoomNumber, ResortFloor, SquareFeet, and RoomTypeID. Set the data type for the RoomNumber field to Short Text and set the remaining data types to Number. Change the field size of the RoomNumber field to 30; set the field size for ResortFloor to Integer; and then set the remaining field sizes to Long Integer, if necessary. Assign RoomNumber as primary key. Save the new table as tblRoom.

In Datasheet view, enter the following data into tblRoom (in this order):

RoomNumber ResortFloor SquareFeet RoomTypeID

101 1 500 1

102 1 520 1

106 1 600 2

206 2 600 2

112 1 700 3

120 1 1000 4

231 2 1400 5

Close the table.

The resort has been tracking inventory items in Excel. Import the spreadsheet a01_grader_a2_Items.xlsx into a new table named tblItem. The first row contains column headings. Do not assign a primary key yet. Do not save the import steps.

Make the following design changes to tblItem. Add a new field, ItemID, as the first field in the table and set it as the primary key. Set the data type to AutoNumber. Remove the field Which rooms. Change the field size of the ItemName field to 20; the ItemDescription field to 40; the Color field to 20; and the ItemCount field to Long Integer. Save and close the table.

Create a new table in Design view. This table will serve as the junction table between tblRoom and tblItem. As the first field, add RoomItemID with a data type of AutoNumber and a description of A unique identifier for the room item (primary key) (no period). As the second field, add RoomNumber, with a data type of Short Text, a description of The room number from tblRoom (foreign key), and a field size of 30. As the third field, add ItemID, with a data type of Number, a description of The item ID from tblItem (foreign key), and a field size of Long Integer. As the fourth field, add InventoryCount, with a data type of Number, a description of Number of items of this type in this room, and a field size of Long Integer. Assign the appropriate field to be primary key. Save the new table as tblRoomItem. Close the table.

Create the following relationships using existing fields and enforcing referential integrity. Do not cascade update or cascade delete. Each room is of a single room type. There are many rooms of each type. A room may have many items in it. Each type of inventory item may be in many rooms. You will need to use the junction table, tblRoomItem to create this relationship.

Create a relationship report accepting the default report name. Close the report and close the Relationships window.

Enter the following data into tblRoomItem (in this order):

RoomNumber ItemID InventoryCount

101 1 1

102 1 1

120 1 1

120 3 1

120 4 1

120 8 4

120 9 3

101 8 2

102 8 2

101 3 1

Close the table.

Use the Report Wizard to create a report showing RoomType and RoomAmenities from tblRoomType and ResortFloor and RoomNumber from tblRoom (in that order). View by tblRoomType and add a grouping by ResortFloor. Sort in ascending order by RoomNumber. Select Landscape orientation. Name your report rptRoomType. In Layout view, change the title of your report to Rooms Listed by Room Type. Save and close the report.

Using the Simple Query Wizard, create a query listing RoomNumber from tblRoom, InventoryCount from tblRoomItem, and ItemName from tblItem (in that order). Save your query as qryTelephone. In Design view, enter criteria that will only return records with items named Telephone in the results. Sort in ascending order by RoomNumber, and then run the query. Autofit column widths. Save and close the query.

Using the Simple Query Wizard, create a query listing RoomNumber and ResortFloor from tblRoom, InventoryCount from tblRoomItem, and ItemName from tblItem (in that order). Save your query as qryRoom120Contents. In Design view, enter criteria that will only return records with room number 120 in the results. Sort in ascending order by ItemName, and then run the query. Autofit column widths. Save and close the query.

Close the database, exit Access, and then submit your file as directed by your instructor.