MS Access

Succeeding in Business with Microsoft Access 2013 Steps to Success Level 2

Hudson Bay Pharmacy is ready to crearte more complex queries to analyze employee data. Kim Siemers, human resources manager for Hudson Bay, asks for your help in creating queries that extract the employee information she needs from their database. As you create and save the new queries be certain to use the "qry" perfix as a part of the naming convention. Also consult your instructor for instruction about submitting results.

1. Start access and open Hudson.accdb database from STS folder.

2. Kim wants to identify employees who live in the same neighborhood in Edmonton so they can create car pools and share rides to work. In Edmonton, the postal codes roughly correspond to neighborhood. Prepare a list of employees who live in the same neighborhood so that Kim can create a list of employees who can share rides to work. Name the query qryDuplicatePostalCodes.

3. A new policy at Hudson Bay Pharmacy is that all employees must acquire and maintain certifications in adult, infant, and child CPR and in using defibrillators. Kim asks you to identify employees who have not completed any certification training. Save the query as qryNoTraining.

4. Kim also needs to list all employees and the classes they have taken. The results should include current employees who have not attended training as well as those who have. Save the query as qryEmployeeTraining.

5. Kim also needs to identify employees whose CPR or defibrillators certification has expired, depending on the time period she specifies. Show all employees whose Adult CPR, Child/Infant CPR, or Defibrillators Use certification has expired in any specified time period. Save the query as qryUpToDate.

6. Mai Yan, manager of Hudson Bay Pharmacy, wants to identify the five current nonsalaried employees who are earning the highest wages per hour. These are the five employees who have been working for the pharmacy the longest or who have regularly received raises for their work. List the top five wage earners of all the current nonsalaried employees. Save the query as qryTop5HourlyRates.

7. To prepare for employee reviews, Kim needs to calculate the minimum, maximum, and average hourly rates for each job category. Provide this information for her, saving the query as qryMaxMinAvgHourlyRate.

8. Mai is considering offering life insurance as an employee benefit, and needs to know the current age of all employees. Provide this information for her. Be certain to provide an appropriate name for the column with the result and to show the ages in descending order. Include the job title and format the results so that they include one decimal place. Also show the first name and last name together. Save the query as qryEmployeeAge.

9. Kim asks you to provide one other statistical analysis. Show the average age of employees by job title. Save this query as qryAvgEmployeeAge.

10. Close the Hudson.accdb database and Access.

Microsoft Access 2010 Chapter 5 Lab 2 Adding Fields and Creating Multitable Forms for the Walburg Energy Alternatives Database

Problem: The management of Walburg Energy Alternatives has found that they need to maintain additional data on suppliers. Management needs to keep track of the last date an order was placed, whether the vendor accepts returns, and whether the vendor allows online ordering. Management also would like to attach to each vendor’s record Excel files that contain historical cost data. Walburg Energy Alternatives requires a form that displays information about the vendor as well as the items that are purchased from vendors.

Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 4. Otherwise, see the inside back cover of this book for instructions on downloading the Data Files for Students, or contact your instructor for more information about accessing the required files.

Perform the following tasks:
1. Add the fields Last Order Date, Returns, Online Ordering, and Cost History to the end of the Vendor table structure. Last Order Date is a Date/Time field, Returns and Online Ordering are Yes/No fields, and Cost History is an Attachment field. Create an input mask for the Last Order Date that uses the Short Date input mask.
2. Add the data shown in Table 5 – 3 to the Vendor table.
Table 5 – 3 Data for Vendor Table
Vendor Code Last Order Date Returns Online Ordering Cost History
AS 3/30/2012 Yes No AS_History.xlsx
JM 3/26/2012 No Yes JM_History.xlsx
SD 4/4/2012 Yes Yes SD_History.xlsx
3. Create the form shown in Figure 5 – 88. Use Vendor Master Form as the name of the form and Items of Vendor as the name of the subform. The title is raised, semi-bold, and distributed with a font size of 24. The labels are blue, bold, and etched with a transparent border style. The fields have a sunken special effect.
4. Open the Vendor Master Form and then open the cost history for Asterman Industries. Change the previous cost for item 4553 to $40.95. Save the change to the workbook.
5. Query the Vendor table to find all vendors that accept returns and allow online ordering. Include the Vendor Code and Name in the query results. Save the query as Returns-Online Query.
6. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 5 Lab 1 Adding Fields and Creating Multitable Forms for the ECO Clothesline Database

Problem: ECO Clothesline needs to maintain additional data on each sales rep. The company needs to add the date each rep started as well as notes concerning the sales rep and a picture of the sales rep. The company wants a form that displays sales rep information and the customers for whom they are responsible.

Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 4. Otherwise, see the inside back cover of this book for instructions on downloading the Data Files for Students, or contact your instructor for more information about accessing the required files.

Perform the following tasks:
1. Add the Start Date, Notes, and Picture fields to the end of the Sales Rep table. Save the changes to the structure of the table.
2. Add the data shown in Table 5 – 2 to the Sales Rep table. Adjust the row and column spacing to best fit the data. Save the changes to the layout of the table.
3. Create the form shown in Figure 5 – 87. Use Sales Rep Master Form as the name of the form and Customers of Sales Rep as the name of the subform. Users should not be able to tab through the Picture control. The title is centered with a font size of 24 and a font weight of bold.
4. Query the Sales Rep table to find all sales reps who started before January 1, 2012, and who have computer skills. Include the Sales Rep Number, First Name, Last Name, and Notes fields in the query results. Save the query as Computer Skills Query.
5. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 5 Apply Your Knowledge
Reinforce the skills and apply the concepts you learned in this chapter.

Adding Date/Time and OLE Fields, Using an Input Mask Wizard, and Querying Date/Time Fields

Instructions: Start Access. If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Babbage CPA Firm database that you used in Chapter 4. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Add the Start Date and Picture fields to the Bookkeeper table structure, as shown in Figure 5 – 84. Create an input mask for the Start Date field. Use the Short Date input mask type.
2. Add the data shown in Table 5–1 to the Bookkeeper table.
3. Query the Bookkeeper table to find all bookkeepers who started after January 1, 2012. Include the Bookkeeper Number, First Name, Last Name, Hourly Rate, and Earnings YTD in the query results. Save the query as Start Date Query.
4. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 4 Lab 2 Presenting Data in the Walburg Energy Alternatives Database

Problem: The management of Walburg Energy Alternatives already has realized the benefits from the database of items and vendors that you created. The management now would like to prepare reports and forms for the database.
Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 3. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Open in Layout view the Inventory Status Report that you created in Chapter 1. Add a total for the Inventory Value field. Be sure the total is completely displayed. Display the average cost. If there are fewer than 10 items on hand, the value should appear in a red bold font. Filter the report for all items
where the number on hand is 5 or less. Save the filtered report as Filtered Inventory Status Report.

2. Create the Items by Vendor report shown in Figure 4 – 84.

3. Create the form shown in Figure 4 – 85. If there are fewer than 10 items on hand, the value should appear in a red bold font. Save the form as Item Update Form.

4. Filter the Item Update Form for all items where the cost is less than $3.00 and sort the results in descending order by cost. Save the form as Filtered Item Update Form.

5. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL

Problem: The manager of the Walburg Energy Alternatives store would like to learn more about SQL and has determined a number of questions he wants SQL to answer. You must obtain answers to the questions posed by the manager.
Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Find all records in the Item table where the difference between the cost of the item and the selling price of the item is less than $0.25 (25 cents). Display the item number, description, cost, and selling price in the query result. Save the query as Lab 7-2 Step 1 Query.
2. Display the item number, description, and profit (selling price – cost) for all items. Name the computed field Profit. Save the query as Lab 7-2 Step 2 Query.
3. Find all items where the description begins with the letter, W. Include the item number and description in the result. Save the query as Lab 7-2 Step 3 Query.
4. Display the vendor name, item number, description, and cost for all items where the number on hand is less than 10. Sort the results in ascending order by vendor name and description. Save the query as Lab 7-2 Step 4 Query.
5. Find the average cost by vendor. Name the computed field Average Cost. Save the query as Lab 7-2 Step 5 Query.
6. Find the total number of reordered items for each item in the Reorder table. Name the computed field Total Ordered. Include the item number in the result. Save the query as Lab 7-2 Step 6 Query.
7. Add the following record to the Reorder table.
Item Number Date Ordered Number Ordered
8590 4/12/2012 3
Save the steps to add the record as Lab 7-2 Step 7 Query.
8. Update the Number Ordered field to 5 for those records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to update the record as Lab 7-2 Step 8 Query.
9. Delete all records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to delete the record as Lab 7-2 Step 9 Query.
10. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 4 Lab 1 Presenting Data in the ECO Clothesline Database

Problem: The management of ECO Clothesline already has realized the benefits from the database of customers and sales reps that you created. The management now would like to prepare reports and forms for the database.

Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 3. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Open in Layout view the Customer Balance Report you created in Chapter 1 and revised in Chapter 3. Modify the report to create the report shown in Figure 4–81. Group the report by Customer Type and sort by Customer Number within Customer Type. Add the Amount Paid field to the report and include totals for the Balance and Amount Paid fields.

2. Create the Customers by Sales Rep report shown in Figure 4–82. Include a total for the Balance field. Change the orientation to landscape. Make sure the total control displays completely. (Hint: Use Layout view to make this adjustment.)

3. Create the Customer Financial Form shown in Figure 4–83. The form includes the date.

4. Create mailing labels for the Customer table. Use Avery labels C2163 and format the labels with customer name on the first line, street on the second line, and city, state, and postal code on the third line. Include a comma and a space after city and a space between state and postal code. Sort the labels by postal code.

5. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 CHAPTER 4 Apply your Knowledge AC 261 and AC 262

Creating a Report and a Form
Instructions: Start Access. If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Babbage CPA Firm database that you used in Chapter 3. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Create the Clients by Bookkeeper report shown in Figure 4–75. The report is grouped by bookkeeper number and sorted by client number within bookkeeper number. Include totals for the Amount Paid and Balance Due fields. Change the orientation to landscape.

2. Create the Client Financial Form shown in Figure 4–76 for the Client table. The form includes the current date and is similar in style to that shown in Figure 4–3 on page AC 212.

3. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 2 Lab 1: Querying the ECO Clothesline Database

Problem: The management of ECO Clothesline has determined a number of questions it wants the database management system to answer. You must obtain answers to the questions posed by management.
Instructions: Use the database modified in the In the Lab 1 of Chapter 1 on page AC 66 for this assignment, or see your instructor for information on accessing the files required for this book.
Perform the following tasks:
1. Open the ECO Clothesline database and create a new query for the Customer table that includes the Customer Number, Customer Name, Amount Paid, and Sales Rep Number fields in the design grid for all customers where the sales rep number is 49. Save the query as Lab 2-1 Step 1 Query.
2. Create a query that includes the Customer Number, Customer Name, and Amount Paid fields for all customers located in Virginia (VA) with a paid amount greater than $1,000.00. Save the query as Lab 2-1 Step 2 Query.
3. Create a query that includes the Customer Number, Customer Name, Street, and City fields for all customers whose names begin with T. Save the query as Lab 2-1 Step 3 Query.
4. Create a query that lists all cities in ascending order. Each city should appear only once. Save the query as Lab 2-1 Step 4 Query.
5. Create a query that allows the user to enter the city to search when the query is run. The query results should display the Customer Number, Customer Name, Balance, and Amount Paid fields. Test the query by searching for those records where the client is located in Ashton. Save the query
as Lab 2-1 Step 5 Query.
6. Include the Customer Number, Customer Name, and Balance fields in the design grid. Sort the records in descending order by the Balance field. Display only the top 25 percent of the records in the query result. Save the query as Lab 2-1 Step 6 Query.
7. Join the Sales Rep and the Customer table. Include the Sales Rep Number, First Name, and Last Name fields from the Sales Rep table. Include the Customer Number, Customer Name, and Balance from the Customer table. Sort the records in ascending order by sales rep’s last name and customer name. All sales reps should appear in the result even if they currently have no customers. Save the query as Lab 2-1 Step 7 Query.
8. Open the Lab 2-1 Step 7 Query in Design view and remove the Sales Rep table. Add the Amount Paid field to the design grid. Calculate the total of the balance and amount paid amounts. Assign the alias Total Amount to the calculated fi eld. Change the caption for the Balance field to Due. Save the query as Lab 2-1 Step 8 Query.
9. Create a query to display the average balance amount for all customers. Save the query as Lab 2-1 Step 9 Query.
10. Create a query to display the average balance amount for sales rep 51. Save the query as Lab 2-1 Step 10 Query.
11. Create a query to display the average balance amount for each sales rep. Save the query as Lab 2-1 Step 11 Query.
12. Create the crosstab shown in Figure 2 – 92. The crosstab groups the total of customers amount paid amounts by state and sales rep number. Save the crosstab as State-Sales Rep Crosstab.
13. Submit the revised database in the format specified by your instructor.