In this project, you will continue to work with the Computer Science Department database from the Chapter 3, Skill Review 3.1. You will add queries to the database to organize the Employees table and to manage equipment on loan. This project has been modified for use in SIMnet.

Skills needed to complete this project:

• Using the Simple Query Wizard
• Adding Text Criteria to a Query
• Specifying the Sort Order in a Query
• Creating a Query in Design View
• Adding Numeric and Date Criteria to a Query
• Hiding and Showing Fields in a Query
• Adding a Calculated Field to a Query
• Finding Unmatched Data Using a Query
• Finding Duplicate Data Using a Query
• Filtering Data Using AutoFilter
• Filtering Data Using Filter by Selection
• Sorting Records in a Datasheet

1. Open the start file AC2013-SkillReview-4-1.

2. If necessary, enable active content by clicking the Enable Content button in the Message Bar.

3. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.

4. Use the Query Wizard to create a select query from the Employees table.

a. On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, verify that Simple Query Wizard is selected. Click OK.

b. Verify that Table: Employees is selected in the Tables/Queries list. Click the >> button to add all the fields to the right. Use the < button to remove the EmployeeID field from the right side. Click Next.

c. In this step, make sure that Detail is selected and click Next.

d. In the last step, type InstructorsByTenure for the title. Select the radio button to Modify the query design and click Finish.

5. Add criteria to the query to return records where the value of the Position field is Adjunct or Faculty.

a. Type Adjunct in the Criteria row under the Position field. Below that, in the or area, type: Faculty

b. Click the drop-down arrow in the Sort row under the LengthOfService field. Select Ascending.

c. Click the Run button.

d. Review the query results, and then save and close the query.

6. Create a query in Design view to return records from the Items table where the value of the Category field is Software and the value of the Cost field is greater than or equal to 199.

a. On the Create tab, in the Queries group, click the Query Design button. In the Show Table dialog, double-click the Items table. Click the Close button.

b. Notice the Items table in the upper pane of the query Design view window. Double-click each field name in the field list except ItemID in order to add them to your query.

c. Type Software in the Criteria row under the Category field.

d. Type >=199 in the Criteria row under the Cost field.

e. Uncheck the Show box under the Category field.

7. Create a calculated field to display a value that is 75% of the Price field value.

a. Next to the Cost field, create a new calculated field by typing the following in the Field row: OurCost: [Cost]*0.75

b. Click the Run button to check your work, and then return to Design view.

c. On the Query Tools Design tab, in the Query Setup group, click the Show Table button. Double- click the Loans table and then click the Close button.

d. Click the Run button and observe the new query results.

e. Save the query with the name: ExpensiveSoftwareOnLoan

f. Close the query.

8. Use the Unmatched Query Wizard to find items from the Items table that do not have corresponding records in the Loans table.

a. On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, click Find Unmatched Query Wizard and click OK.

b. Select Table: Items. Click Next.

c. Select Table: Loans. Click Next.

d. Confirm that Access has selected ItemID in both tables and then click Next.

e. Add the following fields to the query by clicking the > button for each: ItemName, Description, Category. Click Next.

f. Change the name to ItemsNotOnLoan and click Finish.

g. Observe the query results and then close the query.

9. Use the Find Duplicates Query Wizard to find employees who have more than one entry in the Loans table.

a. On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, click Find Duplicates Query Wizard and click OK.

b. Select Table: Loans. Click Next.

c. Select EmployeeID and add it to the right side by clicking the > button. Click Next.

d. Add all fields by clicking the >> button. Click Next.

e. Change the name to EmployeeMultipleLoans and click Finish.

f. Observe the query results and then close the query.

10. Use AutoFilter to filter the Employees table to show only records where the value of the Position field is Technician.

a. Open the Employees table in Datasheet view.

b. Click the arrow in the Position field header. Use the check boxes to make sure that only the Technician option is checked. Click OK and observe the results.

11. Use Filter by Selection to filter the table further to include only employees where the length of service is 10 years or greater.

a. Click in the LengthOfService field for any record where the value is 10.

b. On the Home tab, in the Sort & Filter group, click the Selection button.

c. Click Greater Than or Equal To 10.

d. Save and close the table.

12. Sort the Items table so records are organized alphabetically by category and then by cost from smallest to largest.

a. Open the Items table in Datasheet view.

b. Click anywhere inside the Cost field. On the Home tab, in the Sort & Filter group, click the Ascending button.

c. Click anywhere inside the Category field. On the Home tab, in the Sort & Filter group, click the Ascending button.

d. Save and close the table.

13. Close the database and exit Access.

14. Upload and save the project file.

15. Submit project for grading.

Attachment:- Assignment.rar