Homework answers / question archive / Illustrated Access 2016 | Module 2: SAM Project 1a Scuba Database USING QUERIES   GETTING STARTED Open the file IL_AC16_2a_FirstLastName_1

Illustrated Access 2016 | Module 2: SAM Project 1a Scuba Database USING QUERIES   GETTING STARTED Open the file IL_AC16_2a_FirstLastName_1

MS Access

Share With

---

Illustrated Access 2016 | Module 2: SAM Project 1a

Scuba Database

USING QUERIES

 

GETTING STARTED

• Open the file IL_AC16_2a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as IL_AC16_2a_FirstLastName_2.accdb by changing the “1” to a “2”.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.
  • Hint: If you do not see the .accdb file extension in the Save As file dialog box, do not type it. Access will add the file extension for you automatically.
•  

 

PROJECT STEPS

1. Use the Simple Query Wizard to create a query based on the DiveTrips table with the following options:
2. Include all fields from the DiveTrips table in the query.
3. Select the option to show details in the query.
4. Set the title of the query to DiveTripMasterList.

Open the query in Datasheet View, then save and close it.

1. Open the DiveTripsByLeader query in Datasheet View and change the font of the query to Arial.
2. With the DiveTripsByLeader query still open in Datasheet View, change the font size of the query to 10 pt. Save and close the DiveTripsByLeader query.
3. Create a new query in Query Design View based on the DiveTrips and the DiveMasters tables with the following options. (Hint: Resize the field lists so all fields are visible.)
4. Add the following fields from the DiveTrips table to the query grid in this order: Country, Location, TripStartDate, and Participants.
5. Add the following fields from the DiveMasters table to the query grid, after the Participants field: LName, PhoneNumber.
6. Save the query with the title DivesByCountry.

Open the query in Datasheet View, then close it, saving if necessary.

1. Right-click the DiveLocations query, click Copy, then right-click below the Queries list in the navigation pane and click Paste to create a copy of the DiveLocations query as a new query with the title DiveLocations2020.

Open the DiveLocations2020 query in Design View, then add criteria to select only those records where the TripStartDate field value is greater than or equal to 1/1/2020. Open the query in Datasheet View, confirm that 58 records appear in the DiveLocations2020 query, then close the query, saving if necessary.

1. Create a new query in Query Design View based on the DiveTrips table with the following options. (Hint: Resize the DiveTrips field list to see all of the fields.)
   1. Add the following fields from the DiveTrips table to the query grid in this order: Country, State/Province, City, and Location.
   2. Add an ascending sort order to the Country and State/Province fields.
   3. Save the query with the name SortedByCountry.

Open the query in Datasheet View, then close it, saving if necessary.

1. Right-click the DiveLocations query, then click Copy. Right-click below the Queries list in the navigation pane, then click Paste to create a copy of the DiveLocations query as a new query with the title DiveUSAMexico.

Switch to Design View for the DiveUSAMexico query, then enter criteria to select only those records with a Country field value of USA or Mexico. Open the query in Datasheet View, confirm that 88 records appear in the DiveUSAMexico query, then close the query, saving if necessary.

1. Create a new query in Query Design View based on the DiveTrips table with the following options. (Hint: Resize the DiveTrips field list to see all of the fields.)
   1. Add the following fields from the DiveTrips table to the query grid in this order: Location, Country, TripStartDate, Lodging, and Rating.
   2. Add criteria to select only those tours where the word Grand appears anywhere in the Location field. (Hint: Use two wildcard characters in the criteria.)
   3. Save the query with the name GrandTrips.

Open the query in Datasheet View, confirm that 11 records appear in the datasheet, then close it, saving if necessary.

1. Open the DiveTrips table in Datasheet View, then Filter By Selection to find only those records where the State/Province field equals FL (Florida). Make the following changes to the record for ID 84. (Hint: Do not include the periods shown at the end of substeps a, b, and c in the field values for this record.)
   1. Change the Location field to South Point.
   2. Change the TripStartDate field to 4/1/2020.
   3. Change the Lodging field to Big Seas Resort.

Remove the filter, then save and close the table.

1. Open the LeaderAssignments query in Design View, then enter criteria to select the records with a Rating field value of 5.
2. With the LeaderAssignments query still open in Design View, delete the City and StateProvince fields from the query grid, then delete the Countries table from the query. Open the query in Datasheet View, then save and close it.

Save and close any open objects in your database. Compact and repair your database using the Compact and Repair Database button on the Database Tools tab of the Ribbon, close it, then exit Access. Follow the directions on the SAM website to submit your completed project.