Create a new table in Design view to store conference room details. As the first field, enter ConfRoomID and set the data type for the field to AutoNumber

1

Start Access. Open the downloaded file named a01_grader_h1_HotelEvent.accdb. Save the file with the name a01_grader_h1_HotelEvent_LastFirst, replacing LastFirst with your name. In the Security Warning bar, click Enable Content.

0

2

Create a new table in Design view to store conference room details. As the first field, enter ConfRoomID and set the data type for the field to AutoNumber. Add the description A unique identifier for the conference room (no period). Name the second field RoomName and the third field Capacity. Select the appropriate data type for each. For the RoomName field, enter the description The name of the conference room and change the field size to 40. For the Capacity field, enter the description The capacity of the conference room and change the field size to Integer.

8

3

Assign ConfRoomID as the primary key and then save the table as tblConfRooms. Switch to Datasheet view and add the following three records. In record 1, enter the RoomName Musica and the Capacity 500. In record 2, enter Eldorado with a capacity of 100. In record 3, enter Pueblo with a capacity of 25. Close the table.

5

4

Create a new table in Design view. This table will store groups. Add the following fields (in this order): GroupID, GroupName, ContactFirstName, ContactLastName, and ContactPhone. Set the data type for the GroupID field to AutoNumber and accept the default data type for the remaining fields. Set the field size for the GroupName and ContactLastName fields to 40; set the field size of the ContactFirstName to 30; and set the field size of the ContactPhone to 14.

8

5

Assign GroupID as the primary key. Save the new table as tblGroup. Define an input mask for the contact phone number. Use a mask that will show phone numbers as (555) 555-5555 with a place holder of “_” and save with the symbols in the mask. Close the table.

5

6

Create a new table in Design view. This table will store events.  Add the following fields (in this order): EventID, EventName, EventStart, EventLength, and GroupID. Set the data type for the EventID field to AutoNumber; accept Short Text as the data type for EventName; set EventStart to Date/Time; and set EventLength and GroupID to Number. Change the field size for EventID and GroupID to Long Integer; change the field size for EventName to 40; change the field size for EventStart to Short Date; and change the field size for EventLength to Integer. Assign EventID as the primary key. Save the new table as tblEvent.

10

7

Create a new table in Design view. This table will serve as the junction table between tblConfRooms and tblEvent. Add the following fields (in this order): ReservationID, EventID, ConfRoomID, ReservationDate, and DaysReserved. Set the data type for the ReservationID field to AutoNumber; select the appropriate data type for ReservationDate; and set the remaining data types to Number. Change the field size for the EventID and ConfRoomID fields to Long Integer; change the field format for ReservationDate to Short Date; and change the field size for DaysReserved to Integer. Assign ReservationID as the primary key. Save the new table as tblConfRes. Close the table.

10

8

Open the Relationships window. Add tblGroup, tblEvent, tblConfRes, and tblConfRooms. Create a one-to-many relationship between the correct field in tblGroup and the correct field in tblEvent. Enforce referential integrity. Do not cascade update or cascade delete.

6

9

Create a one-to-many relationship between the correct field in tblEvent and the correct field in tblConfRes. Enforce referential integrity. Do not cascade update or cascade delete. Create a one-to-many relationship between the correct field in tblConfRooms and tblConfRes. Enforce referential integrity. Do not cascade update or cascade delete.

6

10

Create a relationship report and save it with the name Relationships Report. Close the report and close the Relationships window.

4

11

Enter the following data into the appropriate fields in the appropriate tables (in the following order). You may need to determine keys. The group Benson & Diaz Law Group has a contact person of Mary Williams with phone number (505) 555-1207. The group has booked an event called Company Retreat with a start date of 2/17/2018 that has a length of 2 days. They reserved the Pueblo Room for 2/17/2018 for 2 days.

3

12

Enter the following data into the appropriate fields in the appropriate tables (in the following order). You may need to determine keys. The group Dental Association of Nova Scotia has a contact person of Firstname Lastname (with Access as the first name) and the phone number (902) 555-8765. The group has booked an event called Annual Meeting with a start date of 2/17/2018 that has a length of 5 days. They reserved the Eldorado Room for 2/17/2018 for 2 days. They reserved the Pueblo Room for 2/20/2018 for 2 days. Enter the reservations in this order.

3

13

Enter the following data into the appropriate fields in the appropriate tables (in the following order). You may need to determine keys. The group Orchard Growers of the United States has a contact person of Will Goodwin with phone number (212) 555-7889. The group has booked an event called Annual Meeting with a start date of 2/17/2018 that has a length of 2 days. They reserved the Musica Room for 2/17/2018 for 5 days.

3

14

Use the Simple Query Wizard to create a query using RoomName from tblConfRooms, and ReservationDate and DaysReserved from tblConfRes (in that order). Save your query as qryEldoradoRoom. In Design view, enter criteria that will only return records with the room named Eldorado in the results. Sort in ascending order by ReservationDate and run the query. Autofit the column widths. Save and close the query.

8

15

Use the Simple Query Wizard to create a query using data from four tables about the Dental Association of Nova Scotia. The query results should list GroupName, EventName, EventStart, EventLength, RoomName, and ReservationDate (in that order). Save your query as qryDentalAssociation. In Design view, enter criteria that will only return records with the group named Dental Association of Nova Scotia in the results. Sort in ascending order by ReservationDate and run the query. Close the query.

8

16

Create a report from qryDentalAssociation using the Report Wizard. Select all fields. Accept the default view grouping. Sort by RoomName and ReservationDate. Select Landscape orientation. Name your report rptDentalAssociationBooking.

5

17

In Layout view, click on the EventName label and text boxes. In the Property Sheet, on the Format tab, change the Width to 1.5". Click on the EventLength label box. In the Property Sheet, on the Format tab, change the Width to 1". Click on the ReservationDate label and text boxes. In the Property Sheet, on the Format tab, change the Width to 1.5". Use your pointer to move the ReservationDate label and text boxes to the left so that they are on the left side of the dashed line. Modify the report title to be Dental Association Booking. Save and close the report.

8

18

Close the database and exit Access. Submit the database as directed.

0

Total Points

100