Add Totals Row

In “Access” 2007 and 2010 we have the “Totals” function. With this function, we can calculate the sum,count, average, etc… of all records in a “Query” in a relatively simple way.

First open the “Query” in “Datasheet View”, select the “Home” tab in the “Ribbon” and click the “Totals” button.

The “Total Row” is placed under the “New” record row.Each column in our “Query” now has a “Total Field” at the bottom. When we click on it, we see a drop-down menu with different options. A field with a numeric value has more options than a text field.

For example, these are the options for a numeric field:
“Sum”, which is the sum of all the numbers in the column.
“Average” which calculates the average of all numbers in the column.
“Count” which counts the number of records in the column.
“Maximum” that gives the highest value from that column .
“Minimum” that gives the lowest value from the column .

By “standard deviation” we measure how far scattered the values are compared to a mean value. We use “Variance” to calculate the statistical variance of all values in the column.

If you have a text field, we have only the “None” or “Count” options, that seem clear to me .

To remove totals function, click the “Totals” button again in the “Ribbon”.

Find Duplicate records Query

In “Access”, we have a “Query wizard” to find duplicate records in a table fast and easy.

For this, select the “Create” tab in the “Ribbon” and click the “Query Wizard” button.

Select “Find Duplicates Query Wizard ” in the first pane of the “New Query” dialog.

And click the OK button.

In the next window select the table or “Query” in which you want to search for duplicate records.

Click “Next”.

In the next window, select the fields that you want to search for duplicate values.

And click the button with the ‘>’ sign:

Click the “Next” button.

In the next window you determine whether you want to display additional fields in the “Query”.

Click “Next”.
In the next window you give the “Query” is an appropriate name and determine whether you want to display the results or view the design of the “Query”.
Click the “Finish” button:

Find unmatched records Query

In “Access”, we also have the “Query Wizard” to find unmatched records. You might be able to use this example to find articles that are not ordered.

For this, select the “Create” tab in the “Ribbon” and click the “Query Wizard” button.

Select “Find unmatched Query Wizard” in the first pane of the “New Query” dialog.

And click the OK button.

In the second window of the Wizard, select the table that you want to find non-related records for.

For example, in the “Products” table, I want to see items not listed in the related table, I specify that in the next window.
Click the “Next” button

In the third window, I select the “Orders” table and click “Next”.

In the fourth window, I need to select the two fields that are equal on the left part and the right part, in this case, this is the “productID” field and then click the <=> sign
Click the “Next” button.

In the fifth window, you determine which fields you want to show in the “Query”. Select it in the left window and move them to the right window.
Click the “Next” button.
Give the “Query” a name, and click the “Finish” button.

The result:

If you want to remove these items from your table, select it in the “Query” and click the “Delete” key on your keyboard.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats