Feclia wants to create special queries, forms, and reports in the database to help her manage club accounting.To help Felicia finish her work with the database, complete the following steps:

1.Open Access and open the NestEgg database located in the Assignment tab of Blackboard.

2. Open the tblMember and tblContribution tables and make sure that all the property settings match the settings above.

3.For the tblContribution table, specify PaymentID as the primary key and resize all the datasheet columns to best fit. For the tblMember table, add a validation rule for the Level field to store only values that equal 1 or 2, add a validation rule text message and then add the following new Text fields between Address and Phone fields: City (field size 24), State (field size 2 and Caption of State/Prov), and PostalCode (field size 10 and Caption of Postal Code).

4. Modify the first record in the tblMember table datasheet by entering your name, city, state, postal code and phone number, enter phone numbers in the 314-555-1212 format using a Mask.The last four records, enter the same city, state and phone area code, but enter different phone numbers.Then select a second city, state, postal code and phone area code and enter these values in records 2-6 using five different phone numbers.Finally select a third city, state, postal code and phone area code and then enter these values in records 7-11 using five different phone numbers.Resize all datasheet columns to best fit.

5.Define a one-to-many relationship between the primary tblMember table and the related tblContribution table, using Member as the common field, enforcing referential integrity and selecting Cascade Update Related Fields option.Resize the tblMember field list so that all fields are visible and then print the Relationship for NestEgg report. \

6.Create andsave a query named qryDistantMembers that displays the FirstName, LastName, Address, City, State, PostalCode and Phone fields for all members not living in the same city where you live.List the query in ascending order by LastName. Print the query in landscape orientation after testing and saving the query.

7.Create andsave a query named qryMarch17Contributions that displays the FirstName, LastName, Phone and InvestmentAmt fields for all contributions made on 3/17/2019 in ascending order by LastName.Print the query after testing and saving the query

8.Create and save a query named qryInvestmentTotalsByMember that displays each member's first name, last name, and total investment amount.For the calculated field, use the name TotalInvested and a Caption value of Total Invested .Sort in descending order by total investment amount and resize all the columns to best fit.Print the query after testing and saving it.

9.Modify qryInvestmentTotalsByMember by deleting the FirstName and LastName fields and adding PaidDate field as the first field in the query, sort in ascending order by PaidDate.Save the query as qryInvestmentTotalsByDate Print the query.

10.Create andsave a parameter query named qryLevelParameter that displays the FirstName, LastName, Phone, JoinDate and Level fields in ascending order by LastName.If the user doesn't enter a field value, select and display all records.After creating and saving the query, run the query and enter 1 as the Level field value.Print the results.

11.Create a filter that displays all the records that have an investment amount of $400.Print the filter.

12.Use the Form Wizard to create form containing a main form and a subform.Select all the fields from the tblMember table for the main.Specify the title frmContributionsByMember for the main form and the title frmContributionSubform for the subform.Change the text in the main form's title control Contributions By Member and then resize the text box controls in the main form to reduce the widths to accommodate the longest value in the Address field.Change the caption on the record navigation bar for the main form to member and the subform to Contribution.Print only the first main record and its subform.

13.Make a copy of the frmContributionsByMember form and use frmContributionsByMemberModified as the new form's name Modify the new form by a adding a calculated control as shown in Figure 3, that displays the sum of the InvestmentAmt fields that appear in the subform.Set the calculated control's Format property to Currency and set its Tab Stop property to No.If the Form Footer section appears in the subform in your finished form, set its Visible property to No.This step takes you a bit past what you have learned this semester.But you have what you need to complete it

a.You must add a footer in the subform before adding the calculated control in the form.See figure 3b as an example.I am purposely not showing all of the formula, you should be able to figure it out

b.When you create the calculated control in the form, you will need to use the builder or zoom function and a formula similar to this =[frmContributionSubform].[Form]![txtSumInvestmentAmt] Your exact names will depend on what you have called your subform, your total and the txt name in the properties field.

14.Create the custom Contributions by Date report shown in Figure 4.The report contains Page Header, Detail, PaidDate Footer and Report Footer sections.Sort the detail records in ascending order by the PaidDate field and then in ascending order by the LastName field.Hide duplicate values for the PaidDate field.Calculate and print totals of the InvestmentAmt field for each PaidDate field value in grand total.Save the report as rptContributionsByDate .Print the report.

15.Design and create a form named Switchboard .Use figure 5 and the following descriptions as a guide to create the switchboard.

16.There are two ways to approach the switchboard.You can use the document given in the project assignment information, "Switchboard Directions" and use the command buttons.Place the command buttons on the form: the command buttons should use the macros or the button commands, as discussed in the document.I believe this is the simplest approach.You can also go to page 545 and use the Menu System to create a

Navigation Form, which will also use command buttons.Either way is fine and you should end up with your Switchboard looking like figure 5.(If you know how to use Macros, you can also use Macros.They are described in chapter 10)

17.Add the label with the Make your selection text and add the Dollars.jpg picture located in the Assignment tab.

18.Resize the form, and then set form properties.

19.Close the database.