computer homework

Illustrated Access 2013 | Unit B: SAM Project 1a

Illustrated Access 2013

Unit B: SAM Project 1a

Tours Database

USING Queries

PROJECT DESCRIPTION

You work for an adventure tour company that operates recreational hiking, biking, and walking tours in Colorado. The company uses a database to track information about tour guides, tours, tour schedules, clients, and sales. You’ll create and modify several queries to answer specific questions about the data.

GETTING STARTED

· Download the following file from the SAM website:

· IL_Access2013_UB_P1a_ FirstLastName_1.accdb

· Open the file you just downloaded and save it with the name:

· IL_Access2013_UB_P1a_ FirstLastName_2.accdb

· Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically.

· 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

Use the Simple Query Wizard to create a query based on the Tours table with the following options:

a. Include all fields from the Tours table in the query.

b. Select the option to show details in the query.

c. Set the title of the query to TourMasterList.

Run the query, then save and close it.

In Datasheet View, change the SalesListing query font to Times New Roman.

In Datasheet View, change the SalesListing query font size to 12 pt. Save and close the SalesListing query.

Create a new query in Query Design View based on the Tours and Schedule tables with the following options. ( Hint: Resize the Tours field list so all fields are visible.)

d. Add the following fields from the Tours table to the query grid in this order: TourName, Level, TrailheadCity, and Cost.

e. Add the following field from the Schedule table to the query grid, after the Cost field: StartDate.

f. Save the query with the title TourSchedule, then display it in Datasheet View.

Use the Save Object As feature to save the TourSchedule query as a new query with the title TourBeginner.

Open the TourBeginner query in Design View, then add criteria to select only those records where the Level field value equals Beginner. Switch to Datasheet View, confirm the proper records appear in the TourBeginner query, then save and close it.

Create a new query in Query Design View based on the Clients table with the following options. ( Hint: Resize the Clients field list to see all of the fields.)

g. Add the following fields from the Clients table to the query grid in this order: LastName, FirstName, City, and State.

h. Add an ascending sort order to the LastName and FirstName fields.

i. Save the query with the name ClientList, then display it in Datasheet View.

Use the Save Object As feature to save the ClientList query as a new query with the title ClientListTXCO.

Switch to Design View for the ClientListTXCO query, then enter criteria to select only those records with a State field value of TX (Texas) or CO (Colorado). Switch to Datasheet View, confirm the proper records appear in the ClientListTXCO query, then save and close it.

Create a new query in Query Design View based on the Tours table with the following options. ( Hint: Resize the Tours field list to see all of the fields.)

j. Add the following fields from the Tours table to the query grid in this order: TourName, MinimumAge, TrailheadCity, and Cost.

k. Add criteria to select only those tours where the MinimumAge field value is greater than or equal to 10 and the Cost field value is less than or equal to 100. ( Hint: Remember that all records appearing in the query will fulfill both criteria—not just one or the other.)

l. Save the query with the name LowCostTours, display the query in Datasheet View, then close it.

Open the Clients table in Datasheet View, then Filter By Selection to find only those records where the State field equals CA (California). Make the following changes to the record for Greg Huber (ClientID 6) ( Hint: Do not include the periods shown at the end of sub-steps a, b, and c.):

m. Change the Address field to 400 Oak Street.

n. Change the City field to Austin.

o. Change the State field to TX.

Remove the filter, then save and close the table.

Open the SalesListing query in Design View, then enter criteria that use a wildcard character to find all records that have a StartDate field value of any day in the month of June 2015.

In the SalesListing query, delete the SaleDate field from the query grid, then delete the Guides table from the query.

In the SalesListing query, switch to Datasheet View and hide the Cost field. Save and close the SalesListing query.

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

2

image1.tiff