IIHE Education, Access Tutor, Adding Math Functions to a Query

After you have selected the tables, fields, and set the criteria (as shown in Making a Query), you can add math functions.

A query can perform mathematical functions on the records in a table. The secret is turning on the Totals switch. You do this by selecting Totals from the View menu:

When you click in one of the Total cells, a pop-up indicator appears on the right side of the cell. Click once in the Total cell under ICD9_DX_CD, then click on the arrow, and a pop-up menu with a number of choices appears:

The choices provide different ways to look at the data. Here is a summary of the available functions:

Function	Description
Group By	Defines the groups you want to perform the calculations for. For example, to show total Length of Stay by diagnostic code, select Group By for the ICD9_DX_CD field.
Avg	Average of the values in the field. Only works with number and currency fields. Access does not include any Null values in the calculation.
Count	Counts the number of rows in which the field is not a Null value. You can also enter the special expression COUNT(*) in the Field row to count all rows in each group, regardless of Null values.
Expression	Creates a calculated field that includes a function in its expression. Usually, you create a calculated field when you want to use multiple functions in an expression.
First	Returns the first value in the field. This is an advanced function and we recommend that you don't use it.
Last	Returns the last value in the field. This is an advanced function and we recommend that you don't use it.
Max	Returns the highest value found in the field. For numbers, returns the largest value. For text, returns the highest value in collating sequence ("dictionary" order), without regard to case. Access ignores Null values.
Min	Returns the lowest value found in the field. For numbers, returns the smallest value. For text, returns the lowest value in collating sequence ("dictionary" order), without regard to case. It ignores Null values.
StDev	Calculates the statistical standard deviation of all the values in the field. Only works for number and currency fields. If the group doesn't contain at least two rows, Access returns a Null value.
Sum	Total of the values in the field. Only works with number and currency fields.
Var	Calculates the statistical variance of all the values for the field. Only works with number or currency fields. If the group doesn't contain at least two rows, Access returns a Null value.
Where	Specifies a criteria for a field you aren't using to define groupings. If you select this option for a field, Access hides the field in the results by clearing the Show check box. An example would be where you want to only look at hospital stays longer than 2 days.

For this example, we want to look at the number of diabetic admissions for males and females. The first thing we need to do is limit the diagnosis code to diabetes.

Notice that the check box in the Show row is no longer checked. This means that the ICD9_DX_CD field will no longer be displayed. Whenever you choose Where, Show won't be checked. Now let's apply a criteria for ICD9_DX_CD. We want to choose the for diabetes. So type

Now we need to count the number of people. Under LOS, change the Total cell to Count:

We now have finished designing the query. Click the Run button,

, and you should get a query like this:

Interesting stuff. You might want to add a column showing the average length of stay. To do this, you would:

SEX_CD	CountOfLOS	AvgOfLOS
F	34	6.61764705882353
M	56	6.23214285714286

	Adding Math Functions to a Query
IIHE \| Education \| Getting Started With Microsoft Access \| Adding Math Functions to a Query