1
|
Start Access. Open the downloaded file named a01_grader_a2_HotelStaff.accdb. Save the file with the name a01_grader_a2_HotelStaff_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 employees. Add the following fields and descriptions.
Field Name
Data Type
Description
EmployeeID
AutoNumberA unique identifier
for the employee (primary key)
T
AreaIDNumberThe area ID from tblHotelAreas
(foreign key)
|
6
|
3
|
Add the following nine fields to the table (in this order):FirstName,LastName,StreetAddress,City,State,ZipCode,Phone,HireDate, andJobTitle. Change the data type for the HireDate field to Date/Time. Accept Short Text as the data type for the remaining fields. Change the field sizes of the FirstName, LastName, City, and JobTitle fields to30; change the field size of StreetAddress to40; change the field size of State to2; change the ZipCode field size to5; change the field size of Phone to14; and change the field format of HireDate to Short Date. Assign the best field as the primary key and then save the new table astblEmployee.
|
10
|
4
|
Define an input mask for the phone number. Use a mask that will show phone numbers as (555) 555-5555 with a placeholder of "_" and save with the symbols in the mask. Save and close the table.
|
4
|
5
|
Import the downloaded workbooka01_grader_a2_HotelEmp.xlsxfrom Excel using the Employee worksheet, appending it to tblEmployee. Do not save the import steps.
|
6
|
6
|
Import the tblHotelAreas table from the downloadeda01_grader_a2_HotelAreas.accdbAccess database and create a new table. Close the table.
|
6
|
7
|
Import the Area worksheet in the downloaded Excel filea01_grader_a2_HotelEmp.xlsx, appending it to tblHotelsAreas. Do not save the import steps.
|
6
|
8
|
Create a new table in Design view. This table will store information about an employee's schedule. AddScheduleIDas the first field in the table, set the data type to AutoNumber. Add the following four fields to the table (in this order):ScheduleDay,StartTime,HoursScheduled, andEmployeeID. Change the data types for the ScheduleDay and StartTime fields to Date/Time. Change the data types for the HoursScheduled and EmployeeID fields to Number.
|
5
|
9
|
Change the format for ScheduleDay to Short Date; change the format for StartTime to Medium Time; and change the field size for HoursScheduled to Integer. Assign the best field as the primary key, and then save the new table astblSchedule. Close the table.
|
5
|
10
|
Import the Schedule worksheet in the downloaded Excel filea01_grader_a2_HotelEmp.xlsx, appending it to tblSchedule. Do not save the import steps.
|
4
|
11
|
Open the Relationships window. Add tblSchedule, tblEmployee, and tblHotelAreas to the window. Create a one-to-many relationship between EmployeeID in tblEmployee and EmployeeID in tblSchedule. Enforce referential integrity. Do not cascade update or cascade delete.
|
8
|
12
|
Create a one-to-many relationship between AreaID in tblHotelAreas and AreaID in tblEmployees. Enforce referential integrity. Do not cascade update or cascade delete. Save the changes. Create a relationship report accepting the default name. Close the report and close the Relationships window.
|
8
|
13
|
Use the Form tool to create a form for tblEmployee, and then save it asfrmEmployeeSchedule. View the form in Form View. Locate the record for the employee namedGabriel Jones. Add a new record to work on1/3/2018, starting at8 AMand working for8hours. Close the form.
|
7
|
14
|
Use the Simple Query Wizard to create a query. The query results should list AreaID and AreaName from tblHotelAreas, FirstName and LastName from tblEmployee, and ScheduleDay, StartTime, and HoursScheduled from tblSchedule (in that order). Save your query asqryCoffeeShopScheduleand then view the query results.
|
7
|
15
|
Switch to Design view, and then enter criteria that will only return records with AreaID4in the results. Run, and then save the query. Close the query.
|
4
|
16
|
Create a report using the Report Wizard using fields from qryCoffeeShopSchedule. Add the fields FirstName, LastName, ScheduleDay, StartTime, and HoursScheduled (in that order). View by tblSchedule, and group by ScheduleDay. Click Grouping Options and change Group intervals to Day. Sort in ascending order first by StartTime and then by LastName, and change to Landscape orientation. Name your reportrptCoffeeShopScheduleand then finish the wizard.
|
10
|
17
|
View the report in Layout view. Modify the report title to beCoffee Shop Schedule. Save, and then close the report.
|
4
|
18
|
Close the database, and then exit Access. Submit the database as directed.
|
0
|