New Perspectives Access 2013 Tutorial 5: SAM Project 1b Easy Green Lawn Care CREATING SPECIALIZED QUERIES AND ENHANCING A DATABASE PROJECT DESCRIPTION Easy Green Lawn Care provides services such as lawn mowing, mulching, and seeding at fixed hourly rates. Abby Barker, office manager for Easy Green Lawn Care, uses an Access database to generate specific data about the company’s employees, customers, services, and revenue. You will help Abby create queries to answer specific questions about the business. You will also help her modify tables to improve data accuracy. GETTING STARTED ? Download the following file from the SAM website: o NP_Access2013_T5_P1b _FirstLastName_ 1.accdb ? Open the file you just downloaded and save it with the name: o NP_Access2013_T5_P1b_ FirstLastName _2.accdb o Hint : If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically. ? Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Create a query to find all records in the tblCustomer table in which the Address field value contains the word Market anywhere in the field value. Include the following options: a. Display all fields from the tblCustomer table in the query in the following order: CustomerID , CustomerFirst , CustomerLast , Address , City , State , Zip , Phone . b. Sort the query in ascending order by the CustomerLast field in Design View. Save the query as qryMarket , run the query, and then close it. 2. Make a copy of the qryMarket query, rename it qryNotBFH , and then make the following updates: a. Delete the criterion from the Address field. b. Add new criteria to find all records in the tblCustomer table in which the City field values are not Brentwood , Franklin , or Hendersonville . Use a list-of-values match for the selection criteria. c. If the query is not already sorted, sort the query in ascending order by the CustomerLast field in Design View. d. Save and run the query, and then resize the Address column to best fit . Save and close the qryNotBFH query. 3. Create a query to find all records from the tblStaff table in which the MonthlySalary value is 2000 , 2250 , or 2400 . Use a list-of-values match for the selection criteria. The query should have the following options: a. Display these fields from the tblStaff table in the query in the following order: StaffID , OfficeID , StaffFirst , StaffLast , StaffTitle , MonthlySalary . b. In Design View, sort the query in descending order by the MonthlySalary field. Save the query as qryEntry2000s , run the query, and then close it. 4. Create a query to display all records from the tblStaff table with the following options: a. Display the StaffID , OfficeID , and StaffTitle fields (in that order) in the query. b. Sort the query in ascending order by the StaffID field in Design View. c. Add a calculated field named EmpName as the first column in the query that concatenates the StaffFirst field value, a space , and the StaffLast field value. d. Set the Caption property for the EmpName field to Employee Name . e. Save the query as qryEmployeeList , run the query, and then resize the Employee Name column to best fit . Save and close the query. 5. Create a parameter query to select the tblCustomer table records for a Zip field value that the user specifies. Include the following options: a. Display the CustomerFirst , CustomerLast , Address , City , State , and Zip fields (in that order) in the query. b. Use Enter the 5-digit zip code: as the Zip field prompt. If the user doesn’t enter a Zip field value, the parameter query should select all records from the tblCustomer table. c. Sort the query in ascending order by the CustomerLast field in Design View. d. Save the query as qryZipParameter . Confirm the parameter query is working correctly by running the query and entering no value as the Zip field value. Then run the query again and enter 37077 as the Zip field value. Close the query. 6. Create a find duplicates query based on the tblServices table with the following options: a. Select the ServiceName field as the field that might contain duplicates. b. Select the ServiceID , OfficeID , and HourlyServiceFee fields (in that order) as additional fields in the query recordset. Save the query as qryDuplicateServices , run the query, and then close it. 7. Create a find unmatched query that finds all records in the tblCustomer table for which there is no matching record in the tblSales table. The tables are linked by the common CustomerID field. Display the CustomerID , CustomerFirst , CustomerLast , and Phone fields (in that order) from the tblCustomer table in the query recordset. Save the query as qryCustomersWithoutMatchingSales , run the query, and then close it. 8. Create a query to display all fields from the tblStaff table in the following order: StaffID , OfficeID , StaffFirst , StaffLast , StaffTitle , HireDate , and MonthlySalary , sorted in descending order by MonthlySalary in Design View. Use the Top Values property to select the Top 10% . Save the query as qryTop10Percent , run the query, and then close it. 9. Open the tblStaff table in Design View. Change the OfficeID field to a lookup field with the following options: a. Specify that the lookup field values will come from another table or query . b. Select the OfficeID field and the OfficeName field from the tblOffice table. c. Sort the values in ascending order by the OfficeName field. d. Confirm that the Hide the key column (recommended) option is checked. e. Resize the lookup column to best fit . f. Accept the default label for the lookup column. g. Save the changes and view the tblStaff table in Datasheet View. h. Change the OfficeID field value for the record with StaffID 901 (David Stearns) to Tradewind Service Center . Close the table. 10. Open the tblSales table in Design View. Create a field validation rule for the TotalHours field to only allow values greater than or equal to 1 . Enter 1 hour minimum as the validation text. Save and close the tables. Click Yes in the Data Integrity rule prompt. 11. Open the tblCustomer table in Design View. Use the Input Mask Wizard to add an input mask to the Phone field. The input mask should use parentheses as separators for the area code, a space between the area code closing parenthesis and the number, an underscore (_) as the placeholder, and a dash between the second and third groups of numbers, as in (123) 456-7890, with only the last seven digits required. Do not store the literal display characters if you are prompted to do so. Save the tblCustomer table and switch to Datasheet View. Change the Phone field value for the record with CustomerID B505 (David Truax) to (615) 555-2233 . Close the tblCustomer table. 12. View the tblStaff table in Design View and define a field validation rule for the HireDate field. Acceptable field values for the HireDate field are >=1/1/1990 . Use the message Must be on or after 1/1/1990 to notify a user who enters an invalid HireDate field value. Save the table. Click Yes in the Data Integrity rule prompt. Switch to Datasheet View and test the field validation rule for the HireDate field. Make sure any tested field values are the same as they were before your testing. ( Hint : You can confirm this by retyping the correct value or by pressing the Esc key.) Close the table. 13. Create a crosstab query based on the qryServices query with the following options: a. Use the ServiceName field values for the row headings. b. Use the City field values for the column headings. c. Use the sum of the TotalHours field values as the summarized value. d. Include row sums . e. Save the query as qryServiceHoursByCity . f. Run the query, and then resize the columns in the query recordset to best fit . Save and close the query. Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project.