Practice Module 1 – Access (Query, Design View)

Open the FogMachineToYou database. This details a database for a local college fog machine delivery business.

Create a new query based off of the Event table, abiding by the following:

• Display the longest, shortest, and average of the values in the Duration field. Name these columns Longest Event, Shortest Event, and Average Event Duration.
• Format these fields to have 1 decimal.
• Run the query. You should get 1 records.
• Resize columns if necessary to yield the best fit.
• Save this query as Q1_DurationStats.

Copy Q1_DurationStats and rename the copy Q2_EventsByCustomerState. Edit this query based on the following:

• Group by the State field in the Customer table.
• Move the State field so that it’s the first column in the query.
• Run the query. You should get 3 records.
• Resize columns if necessary to yield the best fit.
• Save and close the query.

Modify the Q3_Commission query, and alter it according to the following steps:

• Create a calculated field to determine the 8.5% commission that each FogMachineToYou campus ambassador will receive on each ContractAmount. Name this field to Commission.
• Format this new field as a currency.
• Save the table. (This ensures that the calculated field displays.)
• Remove the Duration field.
• Create a new calculated field that subtracts the Commission field from the ContractAmount field. Name this new calculated field CompanyEarnings.
• Run the query. You should get 14 records.
• Resize columns if necessary to yield the best fit.
• Save and close the query.

Create a new query that displays the list of all cities that FogMachineToYou has sold to, and the sum of all contract amounts associated with those cities. Name this column “Total Revenue”. Additionally, include the number of contracts associated with the cities. Name this column “Count of Contracts”. This will enable the FogMachineToYou executive team to determine in what cities marketing efforts need to be increased.

• Name this query Q4_ContractsByCity.
• Run the query. You should get 4 records.
• Resize columns if necessary to yield the best fit.
• Save and close the query.

Create a new query that determines all the clients who booked an event for Halloween weekend (10/30/2021 – 10/31/2021). FogMachineToYou is doing a Halloween promotion and offering special orange fog to these customers for a $15 increase of their original contract amount. In this new query, you need to show the customer’s name, number, and the promotionally adjusted price (the original contract amount plus $15). The promotionally adjusted price should appear as “Promotional Contract Amount” and should be displayed as a currency.

• Name this query Q5_OrangeFogPromotion.
• Run the query. You should get 6 records.
• Resize columns if necessary to yield the best fit.
• Save and close the query.

Compact and repair the FogMachineToYou database.