Chapter 6 Scored Assignment

Chapter 6: Calculated Fields/Specialized Queries

Objective:  Use MS Access to create a database file of information about Employees, append records from Excel file, query the data based on requested information, make a form and make a report from the data.

Start MS Access

Use the database application (MS Access) to create an Employee database.

• Start MS Access. Start a NEW Blank desktop database
• Access makes you save the file first, before you do anything.
• Save the file on your storage device as Database 6SC XX. Be sure you save to your storage location. Computer lab users make sure you save to your flash drive.

Import Excel data to New Access Table

• Review the Excel file in the Week 8 folder, identifying how many records and fields are in the spreadsheet.  Close the file.
• Import the Excel into the Database 6SC.  Use the External Data group, import Excel button. 
• Find the file and follow the wizard directions to bring in the Excel file, and make sure and choose the setting for Choosing your Own Primary Key, which will be Employee ID.
• Once imported, you should have 17 records and 10 fields.

Navigation Pane/Adjusting Field Sizes

• You should see the table in the navigation pane at the left of your window. 
• Right click and reopen the table in Design View.  Click on the State field. In the General tab below the field list, you’ll see lots of specific setting you can put on fields. This allows more standardization of data and less input errors.  Change the field size from 255 to 2, so users will only be allowed to enter two letters for state. Close and save the table.
• Change the Base Salary field to currency with no decimals.
• Change the Base Bonus field to currency with no decimals.

Add Additional Records

• Add yourself as an employee in the various fields. Use Managerial as department, Vice President of Financial Affairs as job title, and a hire date of 1/31/2021.
• Add another person of your choice. Use Marketing as department, Web Site Designer as job title and hire date of 12/31/2020.

Create Calculated Field

• In Design View, add a new calculated field called Annual Salary, which adds up the [Base Salary] + [Base Bonus] fields.  See notes for setting up the expression to sum up the fields needed.  Format that field with currency, with no decimals on any salary fields.
• 
• The calculated field will not produce results until we build a query to update the Base Bonus field.

Make a Backup File

• Create a Backup of the database file, named Chapter 6 SC Backup_xx.  This will provide a original data if needed at a later time.  It is good practice to backup files, regularly, in case of file corruption.

Create Update Query

• The Bonus Base field can be populated by typing in amounts, or we can create a query that will update the field with a base bonus for each employee.
• Close the Employee Table.  Create a simple query, using the Employee First Name, Employee Last Name, and Base Bonus fields.
• Name the query Base Bonus Update Query.
• Using Design View of the Query, change the Query Type to an Update Query using the Update button, in the Query Type group on the menu.
• The query grid will have an Update To line, where you type 5000 on that line for Base Bonus field.  This will give each employee $5,000.
• Run the query, and note how many changes it made.  Close the query and note if the base bonus was populated and a new annual salary calculated.

Create a Salary Query (Add Total Row)

• Create a simple query showing Employee First Name, Employee Last Name, Base Salary, Base Bonus and Annual Salary field.
• Name the query Salary Query.
• Add a Total Row to the query.  (Home Tab, Totals Button)
• Set each of the numeric fields to SUM.

Create Crosstab Query

• Create a new query using the query wizard to create a crosstab query from the Employee Table.  The crosstab query is a choice in the wizard setup.
• Use these settings to build the query using the step-by-step wizard, be careful to read what the wizard requests on each step.
• Row Heading, Annual Salary
• Column Heading, Employee Last Name
• Use Average function to display the results of the Base Bonus.
• Run the query, widen columns as necessary to see all field names.

Sample Partial Results for Crosstab Query

Create A Parameter Query

• Create a new query using the query wizard to create a simple query from the Employee Table, using Employee First Name, Employee Last Name and Hire Date fields
• Switch to design view and add the parameter of [Enter Date] on the Criteria line of the Hire Date field. You are typing what the user will see when running the query.
• Parameter queries stop when run, and allow the user to enter specific information.
• Run the query, and enter 7/15/1964 as the parameter.  Those employees with that hire date will show.
• Save the query as Parameter Hire Date.

Create a Make Table Query

• Create a new query using the query wizard to create a simple query from the Employee Table, using Employee First Name, Employee Last Name Department and Positions fields
• Switch to design view and change the Query Type to a Make Table Query.  Name the new table Employee Positions and populate the table in the same file.  Save the query name as Make Table Query.
• Run the query, widen fields as necessary to see all the data.
• Close and save.
• Specialized queries have different icons attached to them, indicating they are specialized to perform specific tasks.
• The file should have two tables now.

Find Duplicates Query

• Create a new query using the Find Duplicates query wizard to create a specialized query from the Employee Table, using the zip code field only. Skip the step to show additional fields.
• Name the query Duplicate Zip Codes
• This will find duplicating zip codes in the table, and count them.  Once run, it shows you which zip code is duplicated and how many times.
• Widen fields as necessary.

Create a Delete Query

• Create a new query using the Employee Table.  Use all fields on the query.  Save the query as Delete Query.  
• Switch to design view, choose the Delete Query Type, and add the first name of Lidia on the criteria line of the first name field.
• Run the query, which prompts the deletion of 1 record in the table.
• Save and close the query.

Setup Auto Compact & Repair

• Setup the file to automatically compact and repair when closed.

Submit Files

• Submit both the finished Chapter 6 SC file and the backup file.