Adding a Complex Calculated Field

You have seen how aggregate calculations are created in queries and that they summarize data for a group of records. You can also make calculations on a row-by-row basis, using calculated fields. Calculated fields contain customized expressions. The concatenated Meeting field that we made earlier is a simple example of a calculated field. Calculated fields are created and recomputed each time a query is executed.

In the next query, our eventual task is to generate the GPA for each of the students. To determine this, we will need to use both calculated fields and aggregate functions.

GPA values are calculated by the following formula:

Sum(PointValue*CreditHours)/Sum(CreditHours)

Exiting code block.

We'll create our complex calculated field by combining multiple simpler calculations.

Adding the Weighted Field

The first calculation we need is the weighted grade, by multiplying the PointValue of the student's grade by CreditHours available for the course.

Let's begin by opening a query that is partially completed.

Step1. To open the query,

Double-Click qryGPACalc

We see 486 records. These records are the enrollments where a grade has been assigned.

Let's look at the design of the query.

Step2. To switch to Design View,

Click

You see the following tables in Design View: tblStudents, tblEnrollments, tblGradeScale, tblSections, and tblCourses. In the query design grid, you see the last_name, first_name, student_id, PointValue, and CreditHours fields. Also notice the fields are sorted in ascending order by last_name, first_name and student_id.

Step3. To open a contextual menu, in the query design grid, in the empty column to the right of Credit Hours,

Right-Click the Field row

Step4. To launch the Expression Builder,

Click Build...

NOTE: If Build is grayed out, click another field, then right-click the field row of the empty column again.

The Expression Builder dialog box appears.

Step5. To add the first value to the expression, in the Expression Categories field,

Double-Click PointValue

[PointValue] appears in the Expression field and <Value> appears in the Expression Values field.

Step6. To add the multiplication operator, type:

*

Step7. To add the next value to the expression,

Double-Click CreditHours

We are almost finished building the expression.

Step8. To close the Expression Builder,

Click

The Expression Builder is now closed.

As we saw earlier, Access has named this field "Expr1."

Step9. To replace the field label, in the query design grid,

Double-Click "Expr1", type: Weighted Enter key

We have created an expression that names the new field Weighted and tells Access to multiply the value in the CreditHours field by the value in the PointValue field for each record.

Let's view the query result.

Step10. To view the query result,

Click

There are 486 records returned.

Notice the Weighted column isn't quite wide enough to show us all the data. Let's fix the width before we move on.

Step11. To widen the column, on the right border of the Weighted column,

Double-Click

The Weighted column formatting leaves much to be desired, however we are going to hide this column before we finish, so we won't worry about it.

For each student in the list, we see PointValue, CreditHours, and Weighted:

NOTE: Each student's records may appear in a different order.

Notice that for some students, like Theresa Adams, there are records without a value in the PointValue column. However, these records don't account for all the students who didn't receive grades. We know this because the Enrollments table has 503 records, and we are only seeing 486 records. This can be explained by the relationship between the underlying tables.

Exploring Join Properties

If we examine the enrollments data, we can see the difference can be accounted for by the records where no grade was reported. Why don't these records appear in this dynaset? It's due to the join properties of the relationship between tblGradeScale and tblEnrollments:

The relationship between the tblEnrollments and tblGradeScale tables is established using the Grade field, from tblEnrollments, and the LetterGrade field, from tblGradeScale, as we see in the previous diagram. This relationship is using the join type called inner join. In Access: Structuring & Relating Data, we learned that this type of join only returns the records where the data exists on both sides of the relationship. This means when the Grade field is empty, there isn't a match between the two tables, and these records are excluded from our results.

So what are the records that appear in our results that don't have a PointValue? These are students who received an incomplete in a course. If a student is given an incomplete for a course, the instructor records the grade as an "I." Since the grade "I" appears in both tblEnrollments and tblGradeScale, these records are included in the results. However, there is no point value associated with I grades in tblGradeScale, and if we don't exclude them from the GPA calculation, the results of our calculation will be inaccurate.

Excluding Records Without A PointValue

Let's exclude any records where the PointValue column is blank.

Step1. To return to Design View,

Click

Remember, when a value hasn't been entered into the database, Access sees the value as a null value. In Access 2016: Structuring & Relating Data, we discussed null values and the special keywords we use when working with them.

In this case, we only want to see records where the PointValue field isn't empty or in other words isn't a null value. So the criteria we will enter for the PointValue will be Is Not Null.

Step2. To specify the appropriate criteria, in the PointValue column,

Click the Criteria field, type: Is Not Null Enter key

While we need to add the PointValue field to the query in order to set criteria on the field, it does not actually have to be visible in the results of our query. Remember, we can deselect the Show checkbox to remove the column from being displayed in the results. However for now, as we aren't done with our calculation, we will leave it visible.

Let's view the query result.

Step3. To view the query result,

Click

There are 472 records returned. Notice records for students who received an incomplete are now excluded from the results. Let's save this query.

Step4. To save the changes to this query, press:

Control key + S

Using Aggregate Functions on Calculated Fields

Now that we have eliminated incomplete grades, we need to sum together the rest of the records for each student. Remember, the Totals function allows us to use predefined operations, including Sum, on groups of records.

Remember the equation for GPA is:

Sum(PointValue*CreditHours)/Sum(CreditHours)

Exiting code block.

We created the Weighted calculated column that shows the results of multiplying the PointValue by the Credit Hours. So the equation we are working with at this point is:

Sum(Weighted)/Sum(CreditHours)

What we need to do now is to sum the rows that are for the same student together. So if a student has grades in three classes, the values are added together to give us a sum value for each student's Weighted and CreditHours columns. This will give us the total weighted points the student has earned and the total credit hours each student has taken.

Step1. To return to Design View,

Click

Step2. To turn on the Totals function, in the Show/Hide group of the Query Design tab,

Click

Now let's set the Total row to sum for both Weighted and Credit Hours.

Step3. To select the CreditHours Total row,

Click the CreditHours Total row

Step4. To set the function to sum, in the CreditHours Total row,

Click , Click Sum

Step5. To rename the CreditHours field,

Click before "CreditHours", type: Total Credit Hours: Enter key

Remember, if we didn't do this, the name of the field in our results would be "SumOfCreditHours." We don't need to do it for Weighted since we already renamed it in the last section.

Step6. To set the Weighted aggregate calculation,

Click the Weighted Total field, Click , Click Sum

Step7. To view the query result,

Click

There are now 368 records showing. Notice that many students still appear in multiple rows. That doesn't seem right. Let's explore what the issue is.

Step8. To return to Design View,

Click

The query design grid looks like:

As we showed earlier, the fields with Group By in the Total fields control how many groups there are. Notice that we have Group By in the Total fields for last_name, first_name, student_id, and PointValue.

The fields we use to group when using aggregate functions can drastically change the results we get. For example, if we had selected last_name and first_name, but not included student_id, the final GPA values would be merged together for students with the same name.

The extra rows we're seeing can be explained by the grouping on the PointValue field. The grouping on the PointValue field combines all courses in which a student earned the same grade. There is a separate row for each different grade a student earned; for example, unless a student only earned a B+ in all of her classes, they won't all appear in one single row. We really only want to group on last_name, first_name, and student_id.

However, we don't want to just remove PointValue from the query. It is being used to establish the criteria for eliminating enrollments where the student has an incomplete. It might seem that unchecking the Show checkbox so the field didn't appear in the results would fix this, but that is not the case.

What we need to do is change the Total field from Group By to Where. Setting the Total field to Where allows the field to be used for establishing criteria to limit the data, but not be used to create groups.

NOTE: Setting the Total field to Where isn't required if you do want to group by that field. For example, in the previous query, we added parameter criteria for Rank and also grouped by Rank, so we used Group By.

Step9. To change the Total row setting for PointValue,

Click the Total row, Click , Click Where

Notice that changing the Total row to Where deselected the Show field. Whenever the Total row is set to Where, the Show checkbox is automatically deselected. Access does this because the field is only used to set criteria.

Step10. To view the query result,

Click

There are 175 results returned. We now see a row of data for each student with the sum of their weighted points and a sum of the total credit hours. All we have left to do is to divide the weighted points by the total credit hours.

Step11. To save the changes we have made, press:

Control key + S

The changes are now saved.

Using Results of Aggregate Functions in Calculated Fields

Now that we have the sum total for the values in the Weighted and Total Credit Hours fields for each student, we need to use those values to create another calculated field to divide the value of the Weighted column by the value of the Total Credit Hours column.

Step1. To return to Design View,

Click

Step2. To open a contextual menu, in the query design grid, in the empty column to the right of Weighted,

Right-Click the Field row

Step3. To launch the Expression Builder,

Click Build...

NOTE: If Build is grayed out, click another field, then right-click the field row of the empty column again.

The Expression Builder dialog box opens.

Step4. To start the expression, in Expression Categories list,

Double-Click Weighted

NOTE: If you don't see Weighted, close the Expression Builder and save the query. Then reopen the Expression Builder.

Step5. To add the division symbol, type:

/

Step6. To finish the expression, in Expression Categories list,

Double-Click Total Credit Hours

NOTE: If you don't see Total Credit Hours, close the Expression Builder and save the query. Then reopen the Expression Builder.

Step7. To close the Expression Builder,

Click

The Expression Builder is now closed.

As we have seen previously, the default name Access gives this field is "Expr1." We would like to change this to something descriptive.

Step8. To replace the field label,

Double-Click Expr1, Type: GPA Enter key

If we attempted to view the query right now, we would see a Enter Parameter Value dialog box. Access will show this dialog box whenever it runs across a object name or expression it doesn't understand. Notice for GPA the Total row is currently Group By. While it might seem we would change the Total to Sum, that is not the case. If we did and then tried to view the query results, we would get an error message similar to:

In the table of aggregate functions earlier there was a function called Expression. This is used to return a calculation based on the results of other aggregate functions. This is what we need here since both Weighted and Total Credit Hours are the results of aggregate functions already in the query.

Step9. To set the function to Expression, in the Total row under the GPA column,

Click , Click Expression

Step10. To view the query result,

Click

Step11. Expand the GPA column, if necessary.

We see the results of the calculation. Notice the formatting is not appropriate for GPA values. While we could widen the GPA column so we can see all of the data, but what we really want is to limit the number of decimal places returned.

Formatting a Column

We would like the GPA to have just two decimal places. Let's fix the formatting of the GPA.

Step1. To return to Design View,

Click

Step2. To see the properties for the GPA column, if necessary,

Click an empty part of the column, Click

The Property Sheet pane appears to the right side of our screen.

Step3. To set the format for the field, in the Property Sheet pane,

Click the empty space next to Format, Click , Click Fixed

Step4. To set the number of decimals, in the Decimal Places row, type:

2

Step 5. To close the property sheet pane, in the upper right corner of the pane,

Click 

Step6. To view the query result,

Click

You see:

There are 175 results returned. Before we leave this query, let's hide the Total Credit Hours and Weighted columns.

Hiding Columns in Datasheet View

We needed the Total Credit Hours and Weighted columns to calculate the GPA, however we don't really need to see them in the query results. It would seem that you could just deselect the Show cbeckbox in the query design for these two columns. However, if we tried this, when we viewed the query, parameter dialogs would appear, prompting us for values for both Total Credit Hours and Weighted. Earlier, we mentioned that Access shows parameter dialogs whenever it doesn't understand an object or expression. Deselecting the Show checkbox actually alters the SQL used in the query. In our case, this would mean Access wouldn't know what the values of Weighted and Total Credit Hours were for the GPA calculation.

There is a way around this: we can hide the columns in Datasheet View. Hiding values in Datasheet View doesn't alter the SQL; instead it sets their width to 0. This means they are hidden from display, but the SQL statement does not change.

Step1. To select both the columns,

Point to the Total Credit Hours column heading, Press & Drag over to Weighted

Step2. To hide the columns, on the Home tab of the Ribbon, in the Records group,

Click , Click Hide Fields

Step3. To save the changes to the query, press:

Control key + S

Now the query for calculating the GPA is complete. Before we close this query, let's look at what Access is doing behind the scenes for us.

Understanding Structured Query Language (SQL)

Structured Query Language (SQL) is a standardized data query language that was created for the purpose of working with databases. For those intending to work with server databases such as Oracle and SQL Server, knowledge of SQL is essential. One subset of SQL can be used to form statements that create, retrieve, update, or delete data.

Access automatically translates a query into the Access version of SQL, but it is not necessary to know SQL in order to create queries in Access. Knowing SQL, however, could enable you to customize more powerful Access queries. Also, when working with forms and reports in Access, there will be times when you are asked to save changes to the SQL statement. By exploring the SQL View now, you will be more familiar with the terminology.

We can easily view the SQL version of queries we create. Let's explore the SQL View of this query.

Step1. To select a different view of this query, on the Ribbon,

Click , Click SQL View

When this window first opens, all the text is selected. Let's make it easier to read by deselecting the text.

Step2. To deselect the text,

Click anywhere in the window

You see:

The SQL View displays this query in an SQL statement. Notice SQL keywords appear in all uppercase letters: SELECT, AS, FROM, INNER JOIN, ON, WHERE, GROUP BY, and ORDER BY. Note that each field mentioned in the statement is identified in the format tablename.fieldname.

Step3. Close the query.

We are done working with select queries. Now, we will move on to working with the some of the other types of queries Access allows us to create.