Homework answers / question archive / Shelly Cashman Access 2019 | Module 2: SAM Project 1b City Tours Ltd

Shelly Cashman Access 2019 | Module 2: SAM Project 1b City Tours Ltd

MS Access

Share With

---

Shelly Cashman Access 2019 | Module 2: SAM Project 1b

City Tours Ltd.

Creating Queries

GETTING STARTED

• Open the file SC_AC19_2b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_2b_FirstLastName_2.accdb by changing the “1” to a “2”.
  • 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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_AC19_2b_TourGuideData.xlsx
• 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. City Tours Ltd. is a company that provides tours in a city that is a popular tourist destination for travelers. As a customer service manager for the company, you need to be able to query the database to help make decisions about tours and to answer questions from other employees.
   
   Create a query based on the Guides table in Query Design View with the following options:
   1. Add the GuideID, FirstName, LastName, and StartDate fields to the design grid in that order.
   2. Sort the records in Ascending order by LastName.
   3. Save the query using GuideLastNameSorted as the name.
      
      Open the query in Datasheet View, then close it.
2. Open the HighPriceTours query in Design View and make the following changes to the query:
   1. Delete the Length column from the design grid.
   2. Add criteria to select only those records where the BasicCost field value is greater than 50.
   3. Save the changes to the HighPriceTours query.
      
      Open the query in Datasheet View, then close it.
3. Open the ChildrenOrAdult query in Design View and add criteria to select only those records where the NumAdult field value equal 1 or the NumChild field value equal 1. Save the changes to the query. Open the query in Datasheet View, confirm that six records appear in the ChildrenOrAdult query results, then close the query.
4. Open the ComparisonStartDate query in Design View and make the following changes to the query:
   1. Add the FirstName field to the query design grid. The FirstName field should immediately follow the LastName field.
   2. Add criteria to select only those records where the StartDate field value is less than 1/1/2016.
   3. Set the format to Medium Date for the StartDate field.
   4. Set the caption to DateHired for the StartDate field.
   5. Save the changes to the ComparisonStartDate query.
      
      Open the query in Datasheet View, confirm that the results match Figure 1, then close it.
       

 

 

5. Open the StateNameSort query in Design View and make the following changes:
   1. Move the State field to the beginning of the design grid so that the order of the fields in the grid is State, CustomerID, LastName, and FirstName.
   2. Sort the records in descending order by the State field and in ascending order by LastName.
      
      Save the changes to the query. View the query in Datasheet View, then close it.
6. Open the ChildrenAndAdult query in Design View and add criteria to select only those records where the NumChild field value equal 2 and the NumAdult field value equal 2. Save the changes to the query. Open the ChildrenAndAdult query in Datasheet View, confirm that two records appear in the query results, then close the query.
7. Open the MassachusettsCustomers query in Design View and add criteria to select only those records where the State field value is MA. Save the changes to the query. Open the query in Datasheet View, confirm that four records appear in the query results, then close the query.
8. Because customers often reserve tours over the phone, it is often difficult to know the exact spelling of a particular customer when you need to request more details. Open the CustomerName query in Design View and add criteria to select only those records where the LastName field value begins with the letter M. Save the changes to the query. Open the query in Datasheet View, confirm that five records appear in the query results, then close the query.
9. Many queries require data from more than one table. For example, you may want a query to display the customer last name rather than the customer ID for a tour confirmation. Create a query in Design View based on the Customers and Confirmations tables with the following options:
   1. Add the Customers table and the Confirmations table to the design window.
   2. Add the LastName field from the Customers table to the design grid.
   3. Add the ConfirmationID, TourNumber, and TourDate fields from the Confirmations table to the design grid.
   4. Join the Customers table and the Confirmations table by drawing a line from the CustomerID field in the Customers table to the CustomerID field in the Confirmations table. (Hint: Because the field names are identical in both tables, the line may already be there.)
   5. Sort the records in ascending order by LastName.
   6. Save the query using Customer-Confirmation as the name.
      
      Open the query in Datasheet View, then close it.
10. Because customers live in different states, it is often advantageous to create a query where you can change one criteria using a parameter query. Open the StateParameter query in Design View.
    1. Add parameter criteria to the State field to replace the current "NY" criteria. The new parameter criteria should prompt the user with Enter desired state as the text.
    2. Save the changes to the query.
    3. View the query in Datasheet View. Enter CT when prompted.
       
       Confirm that four records appear in the query results then close the query.
11. Open the TopTours query in Design View. Modify the query to sort the AdultCost amounts in descending order. Change the Return value to display only the top 5 records. Open the query in Datasheet view, then close the query, saving if necessary.
12. Open the TotalAdultCost query in Design View. Modify the query by creating a calculated field. Enter TotalAdultCost: [AdultCost]*[NumAdult] in the Zoom dialog box of the first empty column in the design grid. Save the query. Open the query in Datasheet View, then close the query.
13. Open the AverageCost query in Design View and perform the following tasks:
    1. Add the AdultCost field to the design grid.
    2. Add a Totals row to the design grid.
    3. Select Avg as the calculation in the Total row.
    4. Set the caption to Avg Cost for the AdultCost field.
       
       Save the changes to the query. Open the query in Datasheet View and confirm that it matches Figure 2. Close the query.
        

 

 

14. Open the CustomersWithoutConfirmations query in Design View and perform the following tasks:
    1. Change the join property for the relationship between the Customers and Confirmations tables to select ALL records from the Customers table and only those records from the Confirmations table where the joined fields are equal.
    2. Add the Is Null criteria for the ConfirmationID field and add an Ascending sort order on the LastName field.
       
       Save the changes to the query. Open the query in Datasheet View and confirm that there are nine records in the query result. Close the query.
15. Open the UniqueTours query in Design View. Modify the query to list all tours only once. Save the changes to the query. Open the query in Datasheet View and confirm that there are 10 records in the query result. Close the query.
16. Create a crosstab query based on the NewEngland table with the following options:
    1. Use only data from the NewEngland table in the crosstab.
    2. Use the TourNumber field for the row headings.
    3. Use the State field for the column headings.
    4. Use a Count of the ConfirmationID field as the calculated value for each row and column intersection, and include row sums in the crosstab query.
    5. Save the query using Tour-State Crosstab as the name.
       
       View the query, then save and close it.
17. Export the Guides table as an Excel file (.xlsx) with the name Guides to the same folder as the one that stores your database. Do not export the data with formatting and layout. Save the export steps using Export-Guides as the name. Do not add a description.
18. Use the Import Spreadsheet Wizard to import the data from the Support_AC19_2b_TourGuideData.xlsx support file and append it to the TourGuides table. Do not save the Import steps. Open the TourGuides table in Datasheet View. It should contain 12 records. Close the table.
19. Rename the NewYorkTourists query as NewYorkCustomers in the Navigation Pane.
20. Group the objects in the Navigation Pane by Tables and Related Views.

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