Microsoft office 365 Access 2016

Task 1: Field Validation Rule

Open the Castles table and apply a field validation rule to the AnnualVisitors field so that the user cannot enter a negative number for the number of annual visitors to a castle. In other words, the number entered must be greater than or equal to zero . If the user tries to enter a negative number, they should see a message box that says “Annual Visitors cannot be a negative number.”

Task 2: Table Validation Rule

Open the Homeowners table and apply a table validation rule so that the date the person was born (the Born field) is always earlier (i.e., less than) than the date he or she died (the Died field). If the user violates this rule, they should see a message box that says “Birth date must be earlier than death date.” Note: Because Queen Elizabeth II is still alive, you will see the following message box when you first save the rule and Access tried to test it. Just say YES.

Task 3: Lookup field

In the Castles table, use the Lookup Wizard to add a Lookup column to the Country field . Rather than lookup the values from an existing table or query, you will type the values yourself. Type the following values into a single column when prompted by the wizard. Also when prompted by the wizard, use the name Country as the label for your lookup column.

· England

· Scotland

· Ireland

· Germany

· Belgium

· France

Task 4: Parameter query

Create a parameter query that will list castles and their homeowners (from the Castles and Homeowners tables ) according to a country entered by the user. Display the following columns in your query results: (Task 4 instructions continue on the next page)

· Name (from Castles table)

· Country

· YearBuilt

· Fact (from Castles table)

· Name (from Homeowners table)

· Ruled

· Fact (from Homeowners table)

Task 4 Continued…

If the user chooses not to type in a country and just presses enter instead, display all records . Save your query as qryCountryParameter.

Task 5: Top Values Query

Create a top values query based on the Castles table . Display the Name, Country, YearBuilt, Fact, and AnnualVisitors for the top 3 castle destinations (i.e., the ones with the most annual visitors) in the table. If set up correctly, results should be Neuschwanstein, Stirling, and Windsor, in that order. Save your query as qryTopThreeDestinations.

Task 6: Find Unmatched Query

Use the Query Wizard to find castles without matching homeowners. Accept the default query name when you save it. When run, this query will yield just two results – Beersel and Leamaneh.

Task 7: Form with Subform

Create the following Access form that shows each Castle record with its related contacts in a subform. You can begin by creating your main form with the Form Wizard (name it frmCastles), and then polish it in design view and add the subform (keep the default name for the subform). The style shown below is Office (the default), but you may use a different one if you prefer. Note that only five fields are included on the main form and four in the subform. Make sure that you do not have to scroll horizontally in the subform in order to see the phone number. I’ll make allowances for minor variations in size and spacing, but if you know what you’re doing, your form should look very much like the one shown below.

Task 8: Report

Design the report below. This report is based on the Castles table and shows the Name, YearBuilt, Fact, and AnnualVisitors grouped by Country . Note that the Country group has both a header and a footer. Sections that you are not using should be sized down to 0 in height.

Use the instructions below to guide you, paying extra attention as to which controls go into which report section.

When you are done: Submit the .accdb file through Canvas.

Report Header: Contains label in Book Antiqua font, size 36, and picture of a castle (use your own if you like)

Group Header: Contains country name in Book Antiqua font, size 14, and labels for report details bolded and italicized.

Detail section

Group footer: Contains SUM of annual visitors for the country in question, formatted in red.

End of report

Report footer: Contains SUM of annual visitors for ALL castles on the report, formatted in green.