Case Assignment

Data File needed for this Case Problem: Task.accdb

GoGopher! Amol Mehta, a recent college graduate living in Boulder, Colorado, spent months earning money by running errands and completing basic chores for family members and acquaintances, while looking for full-time employment. As his list of customers needing such services continued to grow, Amol decided to start his own business called GoGopher! The business, which Amol operates completely online from his home, offers customers a variety of services-from grocery shopping and household chores to yard work and pet care-on a subscription basis. Clients become members of GoGopher! by choosing the plan that best suits their needs. Each plan provides a certain number of tasks per month to members for a specified period of time. Amol created an Access database named Task to store data about members, plans, and contracts. He wants to create several new queries and make design changes to the tables. Complete the following steps:

1. Open the Task database located in the Access2 ? Case1 folder provided with your Data Files.

2. Modify the first record in the tbl Member table datasheet by changing the First Name and Last Name column values to your first and last names. Close the table.

3. Create a query to find all records in the tbl Plan table in which the Plan Cost field is 600, 900, or 1500. Use a list-of-values match for the selection criterion, and include all fields from the table in the query record set. Sort the query in descending order by the Plan ID field. Save the query as qry Low Volume Plans, run the query, and then close it.

4. Make a copy of the qry Low Volume Plans query using the new name qry High Volume Plans. Modify the new query to find all records in the tbl Plan table in which the Plan Cost field is not 600, 900, or 1500. Save and run the query, and then close it.

5. Create a query to display all records from the tbl Member table, selecting the Last Name, First Name, Street, and Phone fields, and sorting in ascending order by Last Name and then in ascending order by First Name. Add a calculated field named Member Name as the first column that concatenates First Name, a space, and Last Name. Set the Caption property for the Member Name field to Member Name. Do not display the Last Name and First Name fields in the query record set. Create a second calculated field named City Line, inserting it between the Street and Phone fields. The City Line field concatenates City, a space, State, two spaces, and Zip. Set the Caption property for the City Line field to City Line. Save the query as qry Member Names, run the query, resize all columns to their best fit, and then save and close the query.

6. Create a query to display all matching records from the tbl Plan and tbl Member tables, selecting the Last Name and First Name fields from the tbl Member table and the Plan Description and Plan Cost fields from the tbl Plan table. Add a calculated field named Fee Status as the last column that equals Fee Waived if the Fee Waived field is equal to yes, and that equals Fee Not Waived otherwise. Set the Caption property for the calculated field to Fee Status. Sort the list in ascending order on the Last Name field. Save the query as qry Fee Status, run the query, resize all columns to their best fit, and then save and close the query.

7. Create a query based on the tbl Plan and tbl Member tables, selecting the Last Name, First Name, and City fields from the tbl Member table and the Fee Waived, Plan Description, and Plan Cost fields from the tbl Plan table. The query should find the records in which the City field value is Boulder or Erie and the Fee Waived field value is Yes Save the query as qry Boulder And Erie Fee Waived. Save and run the query, and then close the query.

8. Create a parameter query to select the tbl Member table records for a City field value that the user specifies. If the user doesn't enter a City field value, select all records from the table. Display all fields from the tbl Member table in the query record set. Save the query as qry Member City Parameter. Run the query and enter no value as the City field value, and then run the query again and enter Boulder as the City field value. Close the query.

9. Create a find duplicates query based on the tbl Member table. Select Expiration as the field that might contain duplicates, and select all other fields in the table as additional fields in the query record set. Save the query as qry Duplicate Member Expiration Dates, run the query, and then close it.

10. Create a find unmatched query that finds all records in the tbl Member table for which there is no matching record in the tbl Plan table. Select First Name, Last Name, and Phone fields from the tbl Members table. Save the query as qry Members Without Plans, run the query, and then close it.

11. Create a new query based on the tbl Member table. Display the First Name, Last Name, Phone, Expiration, and PlanID fields, in this order, in the query record set. Sort in ascending order by the Expiration field, and then use the Top Values property to select the top 25 percent of records. Save the query as qry Upcoming Expirations, run the query, and then close it.

12. Use the Input Mask Wizard to add an input mask to the Phone field in the tbl Member table. Create the input mask such that the phone number is displayed with a dot separating each part of the phone number. For instance, if the phone number is (303) 123-4567 it should be displayed as 303.123.4567 for new entries. Test the input mask by typing over an existing Phone column value, being certain not to change the value by pressing the Esc key after you type the last digit in the Phone column, and then save and close the table.

13. Define a field validation rule for the Plan Cost field in the tbl Plan table. Acceptable field values for the Plan Cost field are values greater than 500. Enter the message Value must be greater than 500 so it appears if a user enters an invalid Plan Cost field value. Save your table changes, and then test the field validation rule for the Plan Cost field; be certain the field values are the same as they were before your testing, and then close the table.

14. Define a table validation rule for the tbl Member table to verify that Date Joined field values precede Expiration field values in time. Use an appropriate validation message. Save your table changes, and then test the table validation rule, making sure any tested field values are the same as they were before your testing.

15. Add a Long Text field named Member Comments as the last field in the tbl Member table. Set the Caption property to Member Comments and the Text Format property to Rich Text. In the table datasheet, resize the new column to its best fit, and then add a comment in the Member Comments column in the first record about a job you would do for someone else, formatting the text with blue, italic font. Save your table changes, and then close the table.

16. Designate the Access2 ? Case1 folder as a trusted folder. (Note: Check with your instructor before adding a new trusted location.)

17. Make a backup copy of the database.