Case Problem Data File needed for this Case Problem: Task.accdb GoGopher! Amol Mehta, a recent
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,
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.