Add criteria to this query

Illustrated Access 2016 | Module 6: SAM Project 1a

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.pngIllustrated Access 2016 | Module 6: SAM Project 1a

Dive Trips Database

Improving queries

GETTING STARTED
Open the file IL_AC16_6a_FirstLastName_1.accdb, available for download from the SAM website.

Save the file as IL_AC16_6a_FirstLastName_2.accdb by changing the “1” to a “2”.

0. If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

1. Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS
1. Create a new query in Design View based on the DiveTrips table with the following options:

0. Add the Country and Participants fields from DiveTrips table (in that order) to your query.

0. Add the Total row to the query and sum the Participants field.

0. Save the query with the name TotalsByCountry and run the query. (Hint: The Participants field will be renamed SumOfParticipants when you run this query.)

1. Switch to viewing the TotalsByCountry query in Datasheet View, add the Total row to the datasheet, and sum the SumOfParticipants field in the datasheet, as shown in Figure 1 on the next page. Save and close the TotalsByCountry query.

Figure 1: TotalsByCountry Query

1. Right-click the DivesByStartDate query, then copy and paste it using March9AndHighRatings as the name.

1. Open the March9AndHighRatings query in Design View, add criteria to select only those records with the StartDate field value 3/9/17 and a Rating field value greater than or equal to 4. (Hint: Records returned by this query should meet both criteria.) Switch to Datasheet View, confirm that your query returns the records shown in Figure 2 on the next page, then save and close the query.

Figure 2: March9AndHighRatings Query

1. Right-click the DivesByStartDate query, then copy and paste it using KeyOrLarge as the name. Open the KeyOrLarge query in Design View and make the following updates:

4. Add criteria to select records that contain the text Key anywhere in the City field or that contain a Participants field value greater than 13. (Hint: Records returned by this query should meet one or both criteria.)

4. Add two sort orders to sort the records first in ascending order by the LName field and then in ascending order by the StartDate field.

Switch to viewing the KeyOrLarge query in Datasheet View, confirm the query returns the records shown in Figure 3 below, then save and close the query.

Figure 3: KeyOrLarge Query

1. Create a new query in Design View based on the Divemasters and DiveTrips tables with the following options:

5. Use the LName field from the Divemasters table and the Location, City, and Country fields from DiveTrips table (in that order) in your query.

5. Add criteria using the In operator to select all records in which the Country field is equal to “Australia” or “Fiji”.

Save the query with the name AustraliaFiji, switch to Datasheet View to confirm the query looks similar to the one shown in Figure 4 below, then close the query.

Figure 4: AustraliaFiji Query

1. Use the Crosstab Query Wizard to build a crosstab query based on the DivesByStartDate query, as described below:

6. Select the Country field for the Row headings.

6. Select the LName field for the column headings.

6. Select Participants as the calculated field.

6. Select Sum as the function.

6. Enter Diver_Crosstab as the query name.

Confirm the query appears as shown in Figure 5 on the next page, then close the query. (Hint: Figure 5 only shows a portion of the query. It extends to the right beyond the Lincoln column.)

Figure 5: Diver_Crosstab Query

Open the Revenue query in Design View, then create a calculated field to the right of the TripPrice field named Total that multiplies the TripPrice field by the Participants field. (Hint: The calculated control should be structured as [TripPrice]*[Participants].) Save the Revenue query, display it in Datasheet View (the first few records of which are shown in Figure 6 on the next page), then close the Revenue query.

Figure 6: Revenue Query

1. View the DivemasterBirthdays query in Design View, then create a calculated field to the right of the Birthday field named DivemasterAge using the Zoom dialog box. Create the new field with the following expression that uses the Int (Integer) and Now (Today’s date) functions to calculate the current age of each divemaster:

Int((Now()-[Birthday])/365)

Save the DivemasterBirthdays query, display it in Datasheet View, as shown in Figure 7 on the next page, then close the query. (Hint: Depending on the date you complete this assignment, the values in the DivemasterAge column in your query may not match those in Figure 7.)

Figure 7: DivemasterBirthdays Query

1. Open the ParticipantsByMaster query in Design View and add the Total row to the query. The query should be grouped by the Lname field and should sum the values in the Participants field. Save and run the query, then close it.

1. Open the USADives query in Design View and apply a sort to the query in ascending order by the State field, an ascending order sort by the City field, and an ascending order sort by the StartDate field. Save and run the query, then close it.

1. Create a crosstab query in Design View based on the DivesByStartDate query with the following options:

10. Add the Country, Rating, and Participants fields to the query (in that order).

10. Use the Country field as the row heading.

10. Use the Rating field as the column heading.

10. Use the Participants field as the value for the crosstab query, using the SUM function for the Total row calculation for this field.

d. Save the query with the name CountryRating_Crosstab and run it. Confirm the query looks similar to the one shown as shown in Figure 8 on the next page, then close the query.

Figure 8: CountryRating_Crosstab Query

Use the Report Wizard to create a report based on the DivesByLocation query with the following options:

e. Add the Location, Country, City, and StartDate fields (in that order) to the report.

f. The report should be viewed by Country without any additional grouping fields.

g. The data in the report should be sorted in ascending order by the StartDate field.

h. The report should have the Stepped layout and Portrait orientation.

i. Use Dives by Date as the title for the report.

j. In Layout View, resize the StartDate column so that the data is completely visible and reposition it to the left approximately 0.5” as shown in Figure 9 on the next page.

Save and preview the Dives by Date report to confirm that the first page looks similar to the one shown in Figure 9 on the next page, then close it.

Figure 9: Portion of the First Page of the Dives by Date Report

Save and close any open objects in your database. Compact and repair your database, close it, then exit Access. Follow the directions on the SAM website to submit your completed project.

2