Office 2016 – myitlab:grader – Instructions Your Office Series Vol. 1 YO16_AC_BU01_GRADER_PS2_HW - Room Inventory Database Project Description: For each room at the hotel, the resort wants to track room types and all inventory items such as furniture, artwork, and appliances. You will build an Access database to do this tracking. The database will have four new tables: tblRoomType, tblRoom, tblItem, and a junction table between tblRoom and tblItem, named tblRoomItem. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 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. 0 2 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. 8 3 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. 5 4 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. 5 5 6 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 101 102 106 206 112 120 231 ResortFloor SquareFeet 1 500 1 520 1 600 2 600 1 700 1 1000 2 1400 RoomTypeID 1 1 2 2 3 4 5 10 5 Close the table. 7 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. Updated: 01/29/2016 1 5 YO16_AC_BU01_GRADER_PS2_HW_Instructions.docx Office 2016 – myitlab:grader – Instructions Your Office Series Vol. 1 Step Instructions Points Possible 8 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. 6 9 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. 12 10 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. 10 11 Create a relationship report accepting the default report name. Close the report and close the Relationships window. 5 Enter the following data into tblRoomItem (in this order): 12 13 14 15 RoomNumber 101 102 120 120 120 120 120 101 102 101 ItemID 1 1 1 3 4 8 9 8 8 3 InventoryCount 1 1 1 1 1 4 3 2 2 1 5 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. Updated: 01/29/2016 2 8 8 8 YO16_AC_BU01_GRADER_PS2_HW_Instructions.docx Office 2016 – myitlab:grader – Instructions Step 16 Your Office Series Vol. 1 Points Possible Instructions Close the database, exit Access, and then submit your file as directed by your instructor. 0 Total Points Updated: 01/29/2016 3 100 YO16_AC_BU01_GRADER_PS2_HW_Instructions.docx One Double All Double rooms include a King-size bed with Heavenly Sleep Bedding, clock radio with CD player, coffeemaker, hairdryer, iron, ironing board, make-up mirror, microwave oven, refrigerator, satellite television, thick terry cloth bathrobes, wall safe, and wireless Internet access. Rooms have balconies or patios with an excellent view of our golf course or stunning resort. Two Doubles All Two Double rooms include two Double beds with Heavenly Sleep Bedding, clock radio with with CD player, coffeemaker, hairdryer, iron, ironing board, make-up mirror, microwave oven, refrigerator, satellite television, thick terry cloth bathrobes, wall safe, and wireless Internet access. Rooms have balconies or patios with an excellent view of our golf course or stunning resort. Two Kings All Two King rooms include two King-size beds with Heavenly Sleep Bedding, clock radio with CD player, coffeemaker, hairdryer, iron, ironing board, make-up mirror, microwave oven, refrigerator, satellite television, thick terry cloth bathrobes, wall safe, and wireless Internet access. Rooms have balconies or patios with an excellent view of our golf course or stunning resort. Deluxe Suite All Deluxe Suites have two rooms: a bedroom and a living room. The bedroom includes one King-size bed with Heavenly Sleep Bedding, clock radio with CD Player, coffeemaker, hairdryer, iron, ironing board, make-up mirror, microwave oven, refrigerator, satellite television, thick terry cloth bathrobes, wall safe, and wireless Internet access. The living room has a fireplace. Rooms have balconies or patios with an excellent view of our golf course or stunning resort. Super Suite All Super Suites have three rooms: two bedrooms and a living room. The master bedroom includes one King-size bed. The second bedroom has two Double beds. All beds have with Heavenly Sleep Bedding. Also included are clock radio with CD player, coffeemaker, hairdryer, iron, ironing board, make-up mirror, microwave oven, refrigerator, satellite television, thick terry cloth bathrobes, wall safe, and wireless Internet access. The living room has a fireplace. Rooms have balconies or patios with an excellent view of our golf course or stunning resort. ItemName Iron Deluxe iron Ironing board Pull-out sofa Pull-out sofa Mini refrigerator Refrigerator Telephone Cordless phone ItemDescription Steam iron Cordless steam iron Folding iron board Pull-out Queen-size sofa Pull-out Queen-size sofa 3.4 cubic feet refrigerator 20 cubic feet refrigerator Wired telephone Cordless phone on base Color White Turquoise N/A Turquoise Indigo Stainless Stainless Black Black ItemCount 475 25 500 10 20 300 80 9000 200 Which rooms All except the Grand Villa Suites Grand Villa Suites All Deluxe Suites on first floor Deluxe Suites except those on first Rooms Suites All Suites tes except those on first floor
Purchase answer to see full attachment