Part 1 – Microsoft Access View Glossary Use Access to create a database in which you can store and retrie

Part 1 – Microsoft Access

View Glossary

Use Access to create a database in which you can store and retrieve information about the Rio Salado Theme Park operating departments, their managers, and their employees.

1. Create a new blank database.
2. Save the database with the filename RSC_Theme_Park_Database_MEID.accdb. Replace “MEID” with your actual MEID.

Structure of the Database 

NOTE: Read the requirements for the database and be sure you understand how it should work before creating your design.

You will need to complete the following:

• Create two tables.
• Establish table relationships.
• Create one form.
• Create two queries.
• Create one report.

As you work on the project, remember to follow best practices for creating databases as described in your TestOut materials and the online lesson content.

Create the Tables

Tables and their relationships form the backbone of a relational database. In this database, you will create a table for the Rio Salado Theme Park operating departments and their managers, and a table for employees in each department. When creating fields for your tables, it is important to break down your data into the smallest chunks you can (fine granularity) to make it easier to extract data from the database later. Remember to assign the most appropriate data type to each of the fields and that one of the fields in each of your tables must be set as the Primary Key using the AutoNumber data type. You do not need to enter data records into your tables at this time; you will create a form later in this project for data entry.

Department Table

Create a table named Department Table. At a minimum, your table should include the following fields:

• Department ID
• Department Name
• Manager First Name
• Manager Last Name
• Manager Email Address
• Manager Phone Number

Employee Table

Create a table named Employee Table. At a minimum, your table should include the following fields:

• Employee ID
• Department ID
• Employee First Name
• Employee Last Name
• Employee Date of Hire
• Employee Rate of Pay

Establish Table Relationships

Once the design of the tables has been completed, the next step is to establish relationships between the tables. You will join the Department Table with the Employee Table on common fields through the following tasks:

1. Join the primary key of the Department Table with the foreign key of the Employee Table in a One-To-Many relationship.
2. Enforce referential integrity.
3. Cascade update related fields.
4. Cascade delete related records.

Create a Form

Once the tables have been designed and the relationships have been established, it is time to enter data. You will use one form to enter and edit data in the two tables:

1. Create one form named Department Form that can be used to enter data into both tables.
2. Insert a row below the Employee Table subform. Add a button in the new row to perform the Add New Record action with the text: Add Record.
3. Use the form to enter a minimum of four departments (e.g., Concessions, Rides, Business Office, Guest Services) and a minimum of four employees for each department. Include your name as one of the employees in the Business Office department.
4. Apply the Retrospect theme to the form.

Create a Query

The ability to extract data from one or more tables is one of the most important functions provided by a database. You will now design, save, and run a query to show only the employees in the rides department by following these tasks:

1. Create a query named Ride Employees Query based upon the Employee Table.
2. Include the Employee First Name, Employee Last Name, and Department ID fields.
3. Use a Number Criteria for the Department ID field to select only the employees in the Rides Department.
4. Hide the Department ID field.
5. Save and run the query.

Create a Query

Next, you will design, save, and run a query to show the number of employees per department:

1. Create a query named Number of Employees Query based upon the Department and Employee Tables.
2. Include the Department Name and the Employee ID fields.
3. Use the Total Row to Count the number of employees per department.
4. Save and run the query.

Create a Report

Reports are used to generate printouts from the tables in a database. The Report Wizard can be used to access multiple tables, select fields, and group data in a report:

1. Use the Report Wizard to create a report named Department Report.
2. From the Department Table, include the Department Name and Managers’ First and Last Names.
3. From the Employee Table, include the Employees’ First and Last Names.
4. Group by Department Name.
5. Sort Ascending by Employee Last Name.
6. Use a Stepped Layout in Landscape Orientation and be sure the report fits on one page.

Save and close your RSC_Theme_Park_Database_MEID.accdb database.

———————————————————————————————————————————————-

Part 2 – Microsoft Excel

View Glossary

Use Excel to create a workbook containing three worksheets. The Excel workbook will include employee home contact information and a bank account ledger for the Rio Salado Theme Park.

1. Create a new blank workbook.
2. Save the workbook with the filename RSC_Theme_Park_Workbook_MEID.xlsx. Replace “MEID” with your actual MEID.

Structure of the Workbook 

Your workbook must contain three worksheets:

• Employee Data – This worksheet will contain employee home contact information.
• Bank Account Ledger – This worksheet will show the debits and credits for the Rio Salado Theme Park bank account.
• Line Chart – This worksheet will contain a line chart depicting the daily balance for the Rio Salado Amusement Park bank account.

Employee Data Worksheet

One of the best features of Excel is how easily it can work with data from other programs. Because you already created employee data in Access, you can import that data into Excel to reduce time and effort in creating this worksheet.

1. Import the Employee Table from your RSC_Theme_Park_Database_MEID.accdb database into a new worksheet.
2. Rename the worksheet as Employee Data.
3. Assign the worksheet tab a color of your choice other than the Office default.
4. Resize the table to add the following columns to the right of the Employee Rate of Pay column:
   1. Home Address
   2. City
   3. State
   4. Zip Code
   5. Email Address
   6. Phone Number
5. Enter data into the newly added columns for each employee.
6. Increase the column widths to ensure that you can view the longest data entry in each cell of the worksheet.
7. Hide the following columns:
   1. Department ID
   2. Employee Date of Hire
   3. Employee Rate of Pay
8. Align Left the values in the Employee ID and Zip Code columns.
9. Remove Hyperlinks, if any, from the email addresses.
10. Sort the table by Employee Last Name.
11. Apply Outside Borders to the entire table.
12. Apply an appropriate Table Style of your choice other than the Office default.
13. Freeze the column header row of the table.
14. Use the Page Setup Dialog Box to perform the following tasks:
    1. Set the Page Orientation to Landscape.
    2. Scale to Fit to one page wide by one page tall.
    3. Add a Footer to the worksheet:
       1. Enter your Name in the left section.
       2. Enter your MEID in the center section.
       3. Enter the Course Number and your Section Number in the right section.

Bank Account Ledger Worksheet

The Rio Salado Theme Park wants you to create a new worksheet to keep track of their bank account transactions using the following specifications:

1. Add a new blank worksheet to the workbook.
2. Rename the worksheet as Bank Account Ledger.
3. Assign the worksheet tab a color of your choice other than the Office default.
4. In the first row, insert the title RSC Bank Account Ledger.
5. In the second row, create a new table with the following column headers:
   1. Entry Number
   2. Date
   3. Transaction Description
   4. Debits
   5. Credits
   6. Balance
6. Merge and center the title in the first row over the table columns in the second row.
7. Change the Font Size of the title to 18 pt.
8. Apply a Fill Color of your choice, other than the Office default, to the title cell.
9. Make one entry in the table for each of ten consecutive dates. Be sure to include the Entry Number, Date, Transaction Description, and either a Debit or Credit for each entry. Your first entry should be a deposit (make additional deposits as necessary) so that you do not ever have a negative balance.
10. Use a formula (not a function) with cell references based on the Debits and Credits to calculate the first Balance.
11. Use a formula with cell references based on the Debits and Credits and the previous Balance to calculate the remaining balances.
12. Increase the column widths to ensure that you can view the longest data entry in each cell of the worksheet.
13. Using the Table Tools, add a Total Row to your table and use functions to calculate the Total Debitsand Total Credits.
14. Apply the appropriate Number Formatting to all cells in your table.
15. Align Center the Entry Numbers.
16. Use Conditional Formatting to identify the Top 2 Debits in your table.
17. Apply an appropriate Table Style of your choice other than the Office default.
18. Resolve any Error Messages you might receive in any cells.
19. Freeze the first two rows of the worksheet.
20. Use the Page Setup Dialog Box to perform the following tasks:
    1. Scale to Fit to one page wide .
    2. Add a Footer to the worksheet:
       1. Enter your Name in the left section.
       2. Enter your MEID in the center section.
       3. Enter the Course Number and your Section Number in the right section.

Line Chart Worksheet

The Rio Salado Amusement Park wants a visual depiction of the daily balances from the bank account ledger:

1. Select the Bank Account Ledger worksheet.
2. Insert a Line Chart using the data from the Date and Balance fields.
3. Using the Chart Tools, move the chart to a new sheet named Line Chart.
4. Assign the worksheet tab a color of your choice other than the Office default.
5. Select a Chart Style of your choice other than the Office default.
6. Change the Chart Title to Bank Account Balances.
7. Add horizontal and vertical Axis Titles and add meaningful titles.
8. Add Data Labels.
9. Use the Page Setup Dialog Box to perform the following tasks:
   1. Set the Page Orientation to Landscape
   2. Add a Footer to the worksheet:
      1. Use the Insert File Name button in the center section.

Required Worksheets

Arrange the worksheets in the following order:

1. Employee Data
2. Bank Account Ledger
3. Line Chart

Save and Close your RSC_Theme_Park_Workbook_MEID.xlsx workbook.

Submit Your Assignment

After completing both sections of the project, submit your Access and Excel files following the instructions in the lesson.