Computer Science Question

Purpose

In this assignment, you will practice using a variety of query functionality to include using functions, calculations and parameters. You will also create an action query to update data in a table to demonstrate other ways to manipulate data using queries in Access.

Assignment Instructions

Using the provided sample database, create queries to demonstrate a variety of query functionality using functions, calculations and parameters.

1. Open the sample database.

For this assignment you will use this sample database:

IT163_ModelCarSampleDatabase.accdb

1. Using built-in and aggregate functions, show the total quantity of sales by month.

• Select the Quantity from the SalesLineItems table and the SaleDate from the Sales table. You will need to join the Sales and SalesLineItems tables.

• Using the expression builder to apply a date function to the SaleDate field to display the month name only.

Hint:

The SQL code to generate the name of the month looks like this:

Month

Name(Month([SaleDate])) AS [Month]

In the design view, click on “Builder” in the Query Setup group on the Query Tools Design tab on the ribbon to open the expression builder for the date field. Expand the Functions group. Choose Built-in Functions. Select the Date/Time Category and use the MonthName and Month Expression Values.

• Use the total row to group by month and sum the Quantity.

Hint: In the query design view, click on “Totals” from the Show/Hide group on the Query Tools Design tab on the ribbon.

• Save the query as SalesByMonth. Run the query to see the subset of data produced.

Reference: Ulrich, L. A. & Cook, K. (2019). Access

2

019 for dummies. Wiley.

Chapter 15: “Number Crunching with the Total Row”

Chapter 16: “Express Yourself with Formulas / Hooray for Expression Builder”

Your results should show two columns of data like this (partial results for example only – not actual results):

Month

April

2

SumOfQuantity

1. Incorporate a calculation within a field in a query that will reflect a 10% reduction in retail price for all Products from the Products table.

• Select the

  Description

  and RetailPrice fields from the Products table.

• Add a new field “

  SalePrice

  ” as a third field in your query.

• Incorporate a calculation within SalePrice to show the RetailPrice reduced by 10%. Hint: SalePrice: ([RetailPrice]*0.9)
• Display the Description,Retail Price as

  OrigPrice

  and reduced price as SalePrice in the query results.

• Save the query as ProductsSale. Run the query to see the subset of data produced.

For extra help see Optional Resources for Assignment under the Academic Tools, Library link:

Reference:Calculating Fields in SQL Functions

https://support.microsoft.com/en-us/office/calcula…

Reference: Ulrich, L. A. & Cook, K. (2019). Access 2019 for dummies. Wiley.

Chapter 16: “Express Yourself with Formulas / A Simple Calculation”

Your results should show three columns of data like this (partial results for example only – not actual results):

DescriptionSalePriceOrigPriceBuick Skylark

$25.19	$27.99

1. Sometimes we want to allow the user to interact with the query rather than using fixed values in the where clause. We can do this with a parameter query.

• Create a select query that returns the

  Company

  name from the Customers table for a given state. Use a parameter to prompt the user to enter a two character state code.

Hint: add the prompt like [Enter a state code] in the criteria for the

State

field

• Save the query as CompanyForState. Run the query to see the subset of data produced.

Reference: Ulrich, L. A. & Cook, K. (2019). Access 2019 for dummies. Wiley.

Chapter 16: “Express Yourself with Formulas / Using Parameter Queries to Ask for Help”

Your results might look like this (for a state entered of “

NY

”):

CompanyState

NY

NY

NY

NY

NY

Southwest Softies

Rockin And Rollin

World’s Best Toys

Your Favorite Things

Grandma’s Closet

1. Let’s look at an action query this time. One of the customers has a misspelling in their company name and we want to correct the data in the table.

● Create an update query to update the name of the company “Terriffic Toys” in the Customers table to correct the misspelling in their name.

Hint:

Update to: “Terrific Toys”

Criteria: “Terriffic Toys”

• Save the update query as UpdateCompany. Run the query.

Hint: Access will display a warning dialog that “You are about to update 1 row(s).”Click Yes to acknowledge and accept this update.

• Go to your Customers table and look at the record for CustomerID 13 to ensure the company name has been updated to correct the misspelling of the word terrific.

Reference: Ulrich, L. A. & Cook, K. (2019). Access 2019 for dummies. Wiley.

Chapter 17: “Take Charge with Action Queries / Easy Update”

1. Document your query results.

In a Word document, place the SQL code for each of the four queries above. After the SQL code, place a screenshot of the successful execution from Access showing the correct results were garnered when you ran each query. Start a new page for each query. NOTE: for the update query, you should take a screenshot of the Customers table showing the updated company name for that record since this query will not produce a results set as it is updating the table data not just selecting data.