Module 1 Explanation

The full video explanation is locked. To view it, you must purchase Access Cram Kit, Lifetime Access or CSE 148 Cram Kit Bundle. Already purchased? Click here to log in.

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

Start by opening the FogMachineToYou.accdb file on your computer.

To create a new query, go to the Create tab and select Query Design.

In the window that appears, you should select the Event table since that's what we'll be basing this query off of.

Now, let's zone in on the first part of the first bullet point:

• Display the longest, shortest, and average of the values in the Duration field. Name these columns Longest Event, Shortest Event, and Average Event Duration.

To start, how can we display the longest value for Duration?

We must click on the Duration field within the table. You'll notice that it'll then appear in the Design View boxes below on the left.

After this appears, you'll need to click the Totals button in the top ribbon. This'll enable you to place the longest, or "Max", duration value in your output with the dropdown that appears next to "Total:".

For the shortest and average values, we're going to do the exact same thing we did with the longest value, except instead we'll select the "Min" and "Avg" options instead.

For the next part of the first bullet point...

• Display the longest, shortest, and average of the values in the Duration field. Name these columns Longest Event, Shortest Event, and Average Event Duration.

...we've gotta rename the columns. To do so, I'm going to manually type the desired name of the column before each field name, separated with a ":", like so:

Longest Event: Duration

Shortest Event: Duration

Average Event Duration: Duration

This'll look like so when I do it in Access:

For the second bullet point...

• Format these fields to have 1 decimal.

...we must format these values to only have 1 decimal point. To do this, I'm going to right click on each of the columns in Design View and select Properties. Then, I'm going to set the Format to Standard and the Decimal Places to 1. Here's how that'll look for the first column (make sure to do it for the Shortest Event and Average Event Duration columns as well!):

When you run this query, you'll get the following result. To resize the columns properly, just highlight them all (click the first column, and then hold shift while clicking the last column) and double click on the edge of one of the columns!

To save the query as Q2_DurationStats, we need to right click on Query1 and select Save, then enter the desired name.

To copy Q1_DurationStats, just right click on the query in the lefthand panel and select Copy, then Paste it!

Next, let's address the first bullet point:

• Group by the State field in the Customer table.

To do this, we first need to include the Customer table in our Query Design. However, Customer and Event are joined through Contract, so we must join Contract first, then Customer.

Next, we've just gotta double click the State field. We'll want it to have a Group By value in the "Total:" dropdown.

For the second bullet point...

• Move the State field so that it's the first column in the query.

...we must click and hold onto the small gray bar at the top of the column in Query Design. Then, we must drag to the front!

For the next two bullet points, we need to run the query and properly size the columns like so:

Don't forget to save the query!

Let's start by zoning in on the first bullet point:

• 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.

How can we create a calculated field?

In a new column of our Design Query, we're going to right click and select Build. In the pop-up window that appears, we're going to structure our calculated field like so:

Commission: [ContractAmount] * 0.085

The reason we're going to code this into our calculated field is because we want to take the 8.5% commission...

Commission: [ContractAmount] * 0.085

...from the ContractAmount of each contract...

Commission: [ContractAmount] * 0.085

...and name the column that displays this value "Commission".

Commission: [ContractAmount] * 0.085

To address the next bullet point...

• Format this new field as a currency.

...we must right click on this calculated field and select Properties. Then, we should set the Format to Currency.

For the third bullet point...

• Remove the Duration field.

...we just need to highlight the Duration column in the Query Design, right click, and select Cut.

For the next bullet point, we're going to be creating another calculated field.

• Create a new calculated field that subtracts the Commission field from the ContractAmount field. Name this new calculated field CompanyEarnings.

To do this, we'll click on the next open column in the Query Design and click Build. Then, we'll enter the following formula for this new calculated field:

CompanyEarnings: [ContractAmount] - [Commission]

As stated in the bullet point, this calculated field is going to subtract the Commission field...

CompanyEarnings: [ContractAmount] - [Commission]

...from the ContractAmount field...

CompanyEarnings: [ContractAmount] - [Commission]

...and will name this new calculated field "CompanyEarnings".

CompanyEarnings: [ContractAmount] - [Commission]

For the next two bullet points, run the query and resize the columns if need be!

Lastly, save and close your query!

PAID CONTENT

This is the end of the preview. To unlock the rest, get the Access Cram Kit, Lifetime Access or CSE 148 Cram Kit Bundle.

Already purchased? Click here to log in.