Add “Totals Row”

In “Access” 2007, we have the new function called “Totals”.
With this function, we can calculate the sum, number, average etc… of all records in a “Query” in a relatively simple way.
So, first open the “Query” in “Datasheet View”, select the “Home” tab in the “Ribbon” and click the “Totals” button.

The “Totals Row” is placed below in a “New” record row.
Each column in our “Query” now has a “Total Field” at the bottom.
When we click on it, a drop-down menu with different options is seen.
A field which has a numeric value has more possibilities than a field which has a text value.
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” which is the highest value from the column.
“Minimum” which is 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, only the options “None” or “Count”, which are pretty clear.

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

Find Duplicate records

In “Access”, we have a “Query wizard”query to find duplicate records in a table in a fast and easy way.
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 field 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” or not.
Click “Next”.
In the last window, you specify an appropriate name for the “Query”.

Click the “Finish” button:

To delete the record, select it in the “Query” and click the “Delete” button on your keyboard.

Find Unmatched records

In “Access”, we also have the “Query” 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.
Click the OK button.

In the second window of the Wizard, select the table that you want to find non-related records.
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, so I select the “Orders” table and click “Next”.

In the fourth window, I need to select the two fields that should be equal, from the 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.
You know how to do this by now.

Click the “Next” button.

Give the query a name and click the “Finish” button.

The result is displayed.

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