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.
2. 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.
- 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.
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!
