Case Problem 1

Data File needed for this Case Problem: Job.accdb

GoGopher! Amol Mehta owns and operates a business that 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 Job to store information for the members, payments, plans, and task schedules. The tblTask table contains data about members and the scheduled tasks, the tblCreditCard table contains data about members' credit cards, the tblMember table contains data about members, the tblPlan table contains data about task plans offered to members, and the tblSchedule table contains data about each scheduled task. The database also contains several other objects, including queries, forms, and reports. Amol wants you to define a many-to-many relationship between the tblMember and tblSchedule tables, create a one-to-one relationship between the tblMember and tblCreditCard tables, and create several new queries. To do so, complete the following steps:

1. Open the Job database located in the Access3 \Case2 folder provided with your Data Files.

2. Designate the Access3 \Case1 folder as a trusted folder. (Note: Check with your instructor before adding a new trusted location.) Modify the first record in the tblMember table datasheet so the First Name and Last Name columns contain your first and last names. Close the table.

4. Define a many-to-many relationship between the tblMember and tblSchedule tables, using the tblTask table as the related table. Define a one-to-one relationship between the primary tblMember table and the related tblCreditCard table. Select the referential integrity option and the cascade updates option for the relationships.

5. Create a make-table query based on the tblMember table, selecting all fields from the table except the Street, City, State, and Zip fields, and only those records where the MemberStatus field value is on hold. Use tblSpecialMember as the new table name, store the table in the current database, and then run the query. Save the query as qryMakeOnHold and close the query.

6. Create an append query based on the tblMember table (selecting all fields from the table except the Street, City, State, and Zip fields) that selects only those records where the MemberStatus field value is inactive. Append the records to the tblSpecialMember table and run the query. Save the query as qryAppendlnactive and then close the query.

Create an update query to select all records in the tblSpecialMember table in which the MemberStatus field value is inactive, changing the MemberComments field value to Contact member and run the query. Save the query as qryUpdateSpecialPlan and close the query.

8. Create a delete query that deletes all records in the tblSpecialMember table where the PlaniD field value equals 312. Run the query, save it as qryDelete312, and close the query. Open the tblSpecialMember table, resize all columns to their best fit, and then save and close the table.

9. Create a select query with an outer join between the tblMember and tblCreditCard tables, selecting all records from the tblMember table and any matching records from the tblCreditCard table. Display all fields from the tblMember table, and the CardNum and ExpDate fields from the tblCreditCard table. Save the query as qryMemberCreditCardOuterJoin, and then run and close the query.

Explore 10. In the tblMember table, add an index that allows duplicates using the DateJoined field, change the PlanID index to No, and then save and close the table.

11. Make a backup copy of the database, compact and repair the database, and then close it.

Attachment:- JobMOD6Dario-LozadaRamirez.rar