Find, eliminate, or hide duplicate records in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 2002

The main principle behind designing and using a relational database, such as the ones that you create by using Access, is to store large volumes of data without duplication. Storing and maintaining duplicate data not only costs you money and effort, but can also lead to incorrect data analysis and misinformed business decisions.

It's worth your time to design and maintain your database to avoid data duplication. But despite solid design and good documentation, mature databases, especially those used by several different people, might eventually gather duplicate data. This article provides you with insights and strategies to use when that situation arises.

Duplicate entries in a personal phone book

Duplicate data is confusing and leads to wasted time and effort.

If you suspect or notice duplicate data when working with your forms or reports, you should make eliminating it a top priority. This article describes the different types of duplicate data and explains how to eliminate duplication from your database altogether.

This article also explains how to handle situations where some data may appear to be duplicated, but only because of the way the underlying query is designed and the results it returns. Learn how to avoid displaying redundant data in your current view without deleting any data from the database.

The examples in this article are based on the Northwind.mdb sample database. The version of this database that is installed with Access does not contain any duplicate data, but for the purpose of illustrating the key scenarios described in this article, we modified the database to include a few duplicate records and tables.

ShowAdd duplicate data to the Northwind.mdb sample database — here's how

Get started by adding a few duplicate records in the Customers and Employees tables.

  1. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.

If you see a security warning message, click Open in the dialog box.

  1. In the Database window, under Objects, click Tables, and then double-click the Customers table.
  2. In Datasheet view, add a duplicate record for Antonio Moreno Taquería to the Customers table. Type AMRTQ in the CustomerID field, Antonio Moreno Taquería in the CompanyName field, Helvetius Nagy in the ContactName field, Mataderos 2312 in the Address field, México D.F. in the City field, and (5) 555-3392 in the Phone field.
  3. Add a duplicate record for Blauer See Delikatessen, with a slightly different address. Type BSDKT in the CustomerID field, Blauer See Delikatessen in the CompanyName field, Hanna Moos in the ContactName field, Forsterstr. 169 in the Address field, Mannheim in the City field, and 0711-020361 in the Phone field.
  4. Close the Customers table, and return to the Database window.
  5. Double-click the Employees table. Add a duplicate record for Andrew Fuller. Type Fuller in the LastName field, Andrew in the FirstName field, 19-Feb-1952 in the BirthDate field, and 11-Aug-1992 in the HireDate field. Fill in the remaining fields by copying the values from the original record.
  6. Close the table.

Some scenarios described in this article involve making structural changes to the tables or deleting large numbers of records. The steps for making these changes are described in the corresponding sections.


Working in an ADP?    If you are interested in hiding duplicate data when working in an Access project (ADP), see the topic Exclude duplicate rows in a query (ADP).


What is duplication and what is not

Before you take action to delete duplicate records, you must understand what the term duplication really means. Seeing the same value in a field in two different records or seeing the same field in two different tables is not necessarily duplication. In fact, it might be required for the proper functioning of the database.

For example, you might see the same value in the City field in multiple records in the Customers table or see the CustomerID field in both the Customers and the Orders tables. Multiple customers might very well be located in the same city, and the other fields in the customer records (such as name and address) will contain enough unique information to keep the records from being considered duplicates. And using a field in more than one table is essential to maintaining a one-to-many relationship between the tables so that they can be joined in a meaningful way. Duplicating a primary key in other tables is often called a "necessary redundancy."

Within the context of this article, the term duplication refers to all types of unnecessary redundancies. These redundancies are explained here:

  • Two or more records contain values that wholly or partially match in one or more fields in a table.    Two records might be considered duplicates even though not all fields of the records contain matching values. For example, you see two records for Antonio Moreno Taquería in the Customers table in the modified Northwind.mdb sample database.

The Address and City fields in the two records match, which leads you to suspect that they might be duplicate records. The CustomerID, ContactName, and other fields don't match between the two records, but you still might want to investigate the records and, if it turns out that the records are actually duplicates, delete one of them.

Duplicate records for a customer in the Customers table

A user of the database probably added a second record without checking to see if a record already existed for this customer. Note that the two records have different values for the ContactName field — probably a consequence of duplication. When the customer's contact person changed, only one of the records in the table was updated.

Sometimes, field values in duplicate records might not fully match, due to differences in spelling or typing errors. In such situations, even a partial match might be sufficient to review two records to see if they are duplicates. For example, in the modified Customers table, there are two records with the value "Blauer See Delikatessen", but their Address fields have slightly different values. A quick review of the records suggests that these records might indeed be duplicates.

Two records for a customer where the Address field values partially match

  • All fields displayed in a view contain matching values for two or more records.    In some special situations, you might see what seem to be duplicate records, even though the underlying tables seem to contain only unique records. This problem can arise when your view does not include the fields that could uniquely identify a record. For example, the Ship Status query shows the OrderDate, ShippedDate, and ShipRegion of each of the orders shipped last month, but does not include the OrderID field that would uniquely identify the records.

A view that that does not include a field that could uniquely identify the records

In this view, you see several records that appear to be duplicates, but in reality the records are unique. The records show that two orders, placed on the same day, were also shipped on the same day to the same region. The OrderID field that would uniquely identify each record is not included in the view; hence the records appear to be duplicates. If you were to add the OrderID field to the view, you would see that the records are actually unique.

View now includes the OrderID field to eliminate duplicate records

On the other hand, if it is sufficient to see just one record per region for each set of order dates and shipped dates, you can choose to hide the seemingly duplicate records from your view. But remember that you must not delete any records from the database in such a situation.

  • Two or more tables in the database contain similar data.    For example, you realize that the Northwind.mdb database (the version we've modified) has both a Customers table and a Clients table and that, for some customers, records exist in both tables. Because customers and clients are synonymous, this appears to be a case where two tables contain the same kind of information, although the structures of the tables may differ a bit. You've got some cleaning up to do.

Clients and Customers tables with overlapping data

Here, it looks like someone created a new table and, over time, the distinction between the two tables got lost. Now you have the unenviable job of comparing the two tables and merging them into a single table that contains the most up-to-date information for each customer.

  • Two or more databases contain similar data.    Duplication can also occur at the database level. If you find that two or more databases contain similar data, or you inherit a database that overlaps with your current database, you must compare the data and structure of the databases, and then take necessary steps to consolidate them.

Manually comparing databases, even small ones, is not an easy task. The good news is that several comparison tools are sold by third-party vendors for comparing the contents and structure of Access databases. The Microsoft Office MarketPlace on Microsoft Office Online features several such tools. Visit the Database management section of the Office MarketPlace to check out the currently featured database management tools.

 Note   If, after taking steps to eliminate duplicate data from your tables, you still see duplicate data in a form or report, it might be due to incorrect design of a form or report. Make sure that the underlying tables and queries are joined properly, and that the form or report sections do not include more than one control that is bound to the same control source.

Identifying the course of action to deal with duplication

There are two major factors involved in identifying the right solution:

  1. The nature and extent of duplication    Do you see duplicate data within a single table, or do you see two similar tables (in the same database or in two different databases) with overlapping data? Or do you see duplicate records in a view that is based on two or more related tables?
  2. Your specific requirements    What do you want to do with the duplicate data? Do you want to delete the records from the database, or simply hide them from your view? Or do you want to count, average, or sum the values in the duplicate records? Does it matter which of the duplicate records are deleted or hidden? If it does, do you want to manually review and delete the records, or do it based on a condition? Do you also want to update or consolidate one or more records before you start deleting records?

ShowImportant: Things to keep in mind before you consolidate or delete duplicate records

  • Make sure that the database is not read-only.
  • Make sure that you have permissions to edit or delete records in the database.
  • Ask all other users of the database to close the objects that you want to work with. This helps avoid lock violations.

Tip    To get exclusive access to a database, open the database, and on the Tools menu, click Options. On the Advanced tab, under Default open mode, click Exclusive.

  • Back up your database before you start editing or deleting records. A delete operation cannot be reversed, so there is no way to recover deleted records. A delete operation might also delete records in related tables, so it is best to back up the entire database before starting the operation.

To back up a database, on the File menu, click Back Up Database. In the Save Backup As dialog box, specify a name and location for the backup copy, and then click Save. Access closes the original file, creates a backup, and then reopens the original file.

To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and open it in Access.


The rest of this article is divided into sections with step-by-step procedures. Based on your answers to the preceding questions, click one of the following links to jump to a section that best describes your specific scenario.

I see duplicate data within a single table.    

I have two or more similar tables that contain overlapping data.    

I see duplicate data when querying related tables.    

 Note   After taking action to hide or delete duplicate data, you might want to do some additional tasks, such as compacting the database (to reduce the size of the database) or resetting the AutoNumber field after a delete operation.

For step-by-step information about how to perform these tasks, see the topic Compact and repair an Access file and the Microsoft Knowledge Base article How to reset an AutoNumber field value in Access.

Top of Page Top of Page

Step-by-step procedures

Each of the following procedures addresses a specific scenario involving duplication of data or what may merely appear to be duplication. Observe the results, and you can then apply what you've learned so that you can deal with any duplication problems you may have with your database.

I want to see only records where some fields (but not all) have values that wholly or partially match. Optionally, edit field values and delete records.

Create a query by using the Find Duplicates Wizard. This query will bring up sets of duplicate records that contain the same values in the fields that you specify.

 Note   You will be able to edit the field values or delete records when viewing this query in Datasheet view.

The query that the wizard creates will include only records where values in specified fields match character for character. If you also want to include records where only part of the field values match, you first need to create the query by using the wizard, and then customize the query.

To create the query by using the wizard    

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Find Duplicates Query Wizard, and then click OK.

ShowI get the message "The wizard is not installed or has been disabled"

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.


  1. In the list of tables in the wizard, select the table that contains duplicate records. You want to find duplicate records in the Customers table in the Northwind.mdb sample database, so select the Customers table. Click Next.
  2. In the list of available fields, select the field or fields that contain the duplicate information. Add the CompanyName, Address, and City fields as the basis for determining duplication because two different customers with the same name wouldn't share the same address. Click Next.

Important    Only fields that determine duplication must be included in this step. You can add the additional fields that you want in the query in the next step.

  1. From the list of available fields, select the field or fields that contain the data that you want to inspect or update, based on the duplicate records, such as the ContactName, Phone, and Fax fields. Click Next.
  2. Accept the suggested name (Find Duplicates for Customers), and then click Finish to run the query.

The query shows two records for the customer Antonio Moreno Taqueria

You see two records for the customer Antonio Moreno Taquería. Reviewing the two records, you notice that the contact name and phone numbers are different — and that one of the phone numbers is probably a typing error. If you know which of the two records has the latest information, you can keep that one and delete the other. You can also edit the field values of the record that you want to keep, to bring it up-to-date, before deleting the other record.

You see only these two records for Antonio Moreno Taquería in the query results because no other customer has more than one record with the same name, address, and city information. Although they might actually be duplicates, you don't see the records of Blauer See Delikatessen in the results because the address fields don't completely match in the two records.

To treat records with partial address matches as duplicates, customize the query    

 Note   The following procedure will return records only where there is a partial match. However, if you suspect that a customer has two records with the same name and city but an entirely different address, you need only to rerun the wizard and follow the preceding steps, with one minor difference — include the Address field in step 5, instead of in step 4. If you decide to rerun the wizard, you can ignore the following procedure.

  1. Click the arrow next to the View button on the toolbar, and then click SQL View in the list. You will see the following SQL statement.

SELECT Customers.CompanyName, Customers.Address, Customers.City, Customers.ContactName, Customers.Phone, Customers.Fax
FROM Customers
WHERE (((Customers.CompanyName) In (SELECT [CompanyName] FROM [Customers] As Tmp GROUP BY [CompanyName],[Address],[City] HAVING
Count(*)>1 And [Address] = [Customers].[Address] And [City] = [Customers].[City])))
ORDER BY Customers.CompanyName, Customers.Address, Customers.City;

The WHERE clause uses a second SELECT statement to compare each record against all the other records in the table to identify sets of duplicates.

  1. Now modify the SQL statement to include partial matches in the Address field. The modified statement is given here.
SQL statement Description
SELECT Customers.CompanyName, Customers.Address, Customers.City, Customers.ContactName, Customers.Phone, Customers.Fax No changes. You want to see the same fields, including the entire Address field for each set of duplicate records.
FROM Customers No change.
WHERE (((Customers.CompanyName) In (SELECT [CompanyName] FROM [Customers] As Tmp GROUP BY [CompanyName],Left([Address],7) ,[City] HAVING [Address] has been replaced with the function call Left([Address], 7) to determine duplication, based on the first seven characters in the Address field, as opposed to the entire field value.
Count(*)>1 And Left([Address],7) =Left([Customers].[Address],7) And [City] = [Customers].[City]))) To compare the first seven characters in the Address field of one record with that of another, [Address] has been replaced with the function call Left([Address], 7), and [Customers].[Address] has been replaced with the function call Left([Customers].[Address],7).
ORDER BY Customers.CompanyName, Customers.Address, Customers.City; No change.
  1. Click View Button image to switch to Datasheet view. You will see two sets of duplicates — two records for Antonio Moreno Taquería and two records for Blauer See Delikatessen.

Records where fields wholly or partially match

Top of Page Top of Page

I want to see only distinct records. All fields have identical values in multiple records. If there are duplicate records, show any one record from each set of duplicates, and hide the others.

Most tables include a primary key field or another field, such as social security number or e-mail address, that uniquely identifies each record. If such fields are hidden or are not included when you design the query, you may see two or more records where all fields match.

This section describes the steps needed for viewing distinct records when viewing a query that is based on a single table. If your query is based on more than one table, see the section I want to see only the distinct records. When viewing data from related tables, all fields in the view match. Or if you see duplicate records when viewing a table in Datasheet view, right-click the title bar of the view, and then click Unhide Columns on the shortcut menu. This should add the unique fields to the view.

Now you'll look at a query that is based on the Customers table. It includes only the CompanyName, Address, and City fields from the table, but you'll see that there are two records where all fields match.

A query based on the Customers table, where two records have matching values in all fields

The important thing to remember here is that Access will treat two records as duplicates only if they contain the same value in every field that is included in the view. If the values in even a single field differ, Access will treat them as unique records and will include them in the view. For example, in the previous illustration, there are two records for Blauer See Delikatessen, but because the Address fields have different values, the following procedure will hide neither of these two records. To hide duplicate records such as these, see the next section, I want to see only the distinct records. For duplicate records, I want to specify which record (original, recent, highest, etc.) among the duplicates to include in the view.

  1. Find the query and open it in Design view. If you don't have such a query, you might want to create one so that you can follow the steps given here to suppress duplicate records.

ShowCreate a query based on the Customers table that shows records where all fields match

 Note   You will not be able to edit the field values or delete records when viewing this query in Datasheet view. Usually, select queries based on a single table support editing and deleting data in Datasheet view, but you will be changing the UniqueValues property of this query to hide duplicate records. Setting this property to Yes makes a select query read-only in Datasheet view. To edit or delete data, open the underlying table in Datasheet view.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Simple Query Wizard, and then click OK.

ShowI get the message "The wizard is not installed or has been disabled"

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

  1. Under Tables/Queries, click Customers. Under Available fields, double-click CompanyName, Address, and City. Do not add any other fields because that might cause Access to treat some duplicate records as unique records. Click Next.
  2. Type a name for the query, such as Distinct Customers, click Modify the query design, and then click Finish to open the query in Design view.

  1. Now, to see just one instance of each set of duplicate records, you need to set the query's UniqueValues property to Yes. Right-click the empty area in the top half of the window, and then click Properties on the shortcut menu.

Displaying the query's property sheet

  1. The query property sheet is displayed. Set the UniqueValues property to Yes.
  2. Click View Button image to switch to Datasheet view. You will see the records of all customers but now, for Antonio Moreno Taquería, you will see only one record instead of two.

Query with distinct records

Also note that setting the UniqueValues property made no difference to the query — it still returns two records for Blauer See Delikatessen, where the City field values match but the Address field values are slightly different. It is highly likely that these two records are duplicates — for more information about how to see only one of these two records in your query results, see the next section, I want to see only the distinct records. For duplicate records, I want to specify which record (original, recent, highest, etc.) among the duplicates to include in the view.

Top of Page Top of Page

I want to see only the distinct records. For duplicate records, I want to specify which record (original, recent, highest, etc.) among the duplicates to include in the view.

You will need to create a totals query. Usually, a totals query is used to group and summarize records. You can group records based on one or more fields, and use aggregate functions, such as First, Last, and Max, to summarize each group of records. If you define each set of duplicate records as a separate group, you can then use the aggregate functions to specify conditions, such as the following:

  • Show the original record (the record that was entered first) from each group of duplicate records and hide the others. You will use the First function to do this.
  • Show the most recent record (the record that was entered last) from each group and hide the others. You will use the Last function to do this.
  • Show the record with the smallest or largest value in a specific field. For example, show the record with the most recent date in the OrderDate field. You will use the Max or Min function to do this.

Now you'll create a totals query based on the Employees table. When you view the table in Datasheet view, you will see some records with the same first and last names and matching birth dates.

 Note   You will not be able to edit the field values or delete records when viewing this query in Datasheet view. Totals queries are always read-only in Datasheet view. To edit or delete data, open the underlying table in Datasheet view.

Employees table with a duplicate record for Andrew Fuller

The following steps describe what you need to do to see the original record, the most recently entered record, and the record with the oldest hire date for each set of duplicates.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, double-click the Employees table, and then click Close to close the dialog box.
  4. Add the fields that determine whether two or more records are duplicates. In this case, add the FirstName, LastName, and BirthDate fields to the design grid. The order in which you add the fields does not matter.
  5. If you want to select records based on the value in a specific field, add that field to the design grid. If you want to select the original or most recently entered record, drag any one table field, such as Address or HomePhone, to the design grid. In this example, add the HireDate field to the grid to select records based on hire date.

 Note   Do not add any more fields to the grid. A totals query must only include the fields that determine duplication and one other field that specifies the selection criteria.

  1. Click Totals Button image on the Query Design toolbar. This adds the Total row to the grid — this row displays Group By in all the columns.

Totals query that groups records based on the values that determine duplication

  1. Leave the Total row of the fields that determine duplication (the first three columns) set to Group by. This places all records that have the same values in the FirstName, LastName, and BirthDate fields together in Datasheet view.
  2. Now specify the condition for selecting one record from among the duplicates:
    • To view the original record, change the Total row of the HireDate field to First.
    • To view the most recently entered record, change the Total row of the HireDate field to Last.
    • To pick a record based on the value in a specific field, you need to use the Max or Min function. In this example, you want to view the record with the oldest hire date. To do this, change the Total row of the HireDate field to Min.

Using the Min or Max function to select one record from a set of duplicates

  1. Click View Button image to switch to Datasheet view. You will see the records of all employees — but for Andrew Fuller, who has two records, you will only see the record that has the oldest date in the HireDate field.

The record with the oldest hire date is included  in the view for each employee.

You will use this query in the next section, I want to delete records that are duplicates of others, so save the query as Employees - Distinct Records before closing it.

Top of Page Top of Page

I want to delete records that are duplicates of others.

After you confirm that a table contains duplicate records, you might want to take action to remove the duplication. There are two ways to delete duplicate records:

  • Manually review the duplicate records and delete the records that you don't need.    This technique works for tables that contain just a few duplicate records. The advantage of using this technique is that you can view and edit the field values to bring a record up-to-date before deleting its duplicates.

For step-by-step information, see the section I want to see only records where some fields (but not all) have values that wholly or partially match. Optionally, edit field values and delete records.

  • Delete duplicates by using a query.    You use a delete query to delete all records that are duplicates of others. You will first create a totals query, where you will specify which record among a set of duplicates you want to keep — the original record, the most recently entered record, or the record with the highest or lowest value in a specific field. You will then create a delete query to delete all the records in the table that are not included in the totals query.

Using a delete query is faster than manually deleting records and works well for large tables, or for tables that contain numerous duplicate records. However, you won't be able to edit or consolidate the records before they are deleted, as you could by manually reviewing duplicate records.

Now you'll reuse the totals query named Employees - Distinct Records that you created in the previous section, I want to see only the distinct records. For duplicate records, I want to specify which record (original, recent, highest, etc.) among the duplicates to include in the view. This query includes a record for each employee, but if an employee has duplicate records, the results include the record that has the oldest date in the HireDate field.

To create the delete query    

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, on the Both tab, double-click the Employees table and the Employees - Distinct Records query. Then click Close.
  4. Join the two tables on the LastName field. Drag the LastName field in the Employees table to the LastName field in the Employees - Distinct Records query.

Important    Joining the two tables is necessary to avoid the "Could not delete from the specified tables" message that you will otherwise get when you run the query.

  1. In query Design view, click the arrow next to Query Type on the toolbar, and then click Delete Query in the list.
  2. Drag the asterisk (*) from the Employees table to the Field row of the first column.

The asterisk (*) represents all the fields in the table

You now see Employees in the Table row and From in the Delete row. This means the query will delete rows from the Employees table.

  1. Now you need to specify which records must be deleted. The Employees table contains all distinct and duplicate records, whereas the totals query returns only distinct records. The records in the Employees table that are not returned in the totals query are the duplicate records, so you'll delete them.

Add the LastName, FirstName, BirthDate, and HireDate fields from the Employees table to the grid. Next you need to specify the criteria. To avoid typing the long query name in each Criteria row, assign a short alias to the totals query. Right-click the title bar of the totals query window that lists the fields in the totals query, and then click Properties on the shortcut menu. Set the Alias property to Distinct and close the property sheet. Now type the criteria in the columns in the grid. In the Criteria row of the LastName column, type [Distinct].[LastName]. In the Criteria row of the FirstName, BirthDate, and HireDate columns, type [Distinct].[FirstName], [Distinct].[BirthDate], and <> [Distinct].[MinOfHireDate] respectively, as shown in the following illustration.

Criteria for deleting the duplicate records

  1. Click View Button image to preview the records that will be deleted when you run the query. You will see the record of Andrew Fuller that has the later hire date. Save and close the view.

The record that will be deleted when you run the delete query

Important    The field in the totals query that uses the First, Last, Min, or Max function is the field that separates the records that you want to keep from the ones that are duplicates (that you want to delete). If this field has the same values for two duplicate records, the delete query will not work properly. To be specific, if there are two duplicate employee records with the same hire date, the query will not delete either of the records, even though the underlying totals query returns only one of the two duplicate records. If you expect that the HireDate field may often contain the same values for duplicate records, you need to change the design of the totals query so that it instead uses the HireDate field as a GroupBy field, and some other field with the First, Last, Min, or Max function.

  1. There is one more step to perform before you run the query. You need to review how records in related tables will be affected when the employee record is deleted. If you run the query without doing this step, the query will not be able to delete any records, due to key violations.

In the Database window, right-click the window background, and then click Relationships to open the Relationships window. Identify the tables that are related to the Employees table.

Tables that are related to the Employees table

You see that the Orders table is related to the Employees table. Each record in the Orders table contains the ID of the employee who handled the order. If you delete an employee record, the orders that reference the ID of the deleted record will then reference an invalid ID. To avoid this, you should review and edit the records in the Orders table to make sure that all orders handled by Andrew Fuller reference the ID of the record that you are going to keep.

In some cases, the appropriate thing to do will be to delete all dependent records. For example, if you are deleting a category record from the Categories table, you might want to delete all the products that fall in that category from the Products table. To make sure that the delete query will delete all dependent records, double-click the middle segment of the line joining the Categories and Products tables, and see if the Enforce Referential Integrity and Cascade Delete Related Records check boxes are selected. If not, select both. Repeat this step for all tables that depend on the Products table, and so on, until you account for all dependent tables.

Deleting a category record will delete all product records that fall in the same category

  1. Close the Relationships window and return to the Database window. Find the delete query and double-click it. Click Yes twice to confirm that you want delete records by using the query.

ShowI don't want to see these message boxes each time I run this query

  • On the Tools menu, click Options. Click the Edit/Find tab. Under Confirm, clear the Action queries check box.

  1. If you followed all the steps, and you have permissions to delete records in the database, and the database is not locked or read-only, the delete query will succeed in deleting the duplicate records. Open the Employees table in Datasheet view to verify that the duplicate record for Andrew Fuller has been deleted. If you enabled cascade deletes, open the dependent tables and verify that the related records have been deleted as well.

Top of Page Top of Page

I want to count the duplicate records or calculate the sum or average of the values in a field for each set of duplicate records.

Sometimes you might want to count the number of duplicate records or summarize the data for each of the duplicates before taking any steps to consolidate or remove duplication. For example, you might want to count the number of records that you have for each customer to assess the extent of duplication, or summarize a specific field to make some quick financial decisions.

Consider the modified Customers table in the Northwind.mdb sample database that includes a field named AmountDue. This field contains the amount that the customer owes Northwind Traders. The table includes duplicate records for a few customers.

Customers table with the AmountDue column and some duplicate records

 Note   Before you get started with the following procedure, you might want to add the AmountDue field to the Customers table and populate it. In addition, you might want to add records for a few customers where the name, address, and city match those of existing records.

Now you'll create a totals query that uses the aggregate functions, such as Count, Sum, and Average, to summarize the duplicate data in this table.

 Note   You will not be able to edit the field values or delete records when viewing this query in Datasheet view. Totals queries are read-only in Datasheet view. To edit or delete data, open the underlying table in Datasheet view.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, double-click the Customers table, and then click Close to close the dialog box.
  4. Add the fields that determine whether two or more records are duplicates. In this case, add the CompanyName, Address, and City fields to the design grid. The order in which you add the fields does not matter.
  5. Click TotalsButton image on the Query Design toolbar. This adds the Total row to the grid.
  6. Set the Total row of the CompanyName, Address, and City columns to Group By.
  7. Do one of the following, depending on your requirements:
    • To count the number of records that are in the table for each customer, add the CustomerID field to the grid. Always use a field that does not contain any blank values as the basis for counting records. Set the Total row to Count.
    • To count the number of phone or fax numbers contained in the duplicate records, add the Phone or Fax field to the grid. Set the Total row to Count.
    • To calculate the total amount due from the customer, add the AmountDue field to the grid. Set the Total row to Sum.

Totals query that summarizes duplicate data in Design view

 Note   A totals query can only display summary information. You cannot show the individual phone numbers or customer IDs.

  1. Click View Button image to switch to Datasheet view. You will see one record for each unique customer. The fourth, fifth, and sixth columns will display the summary information.

Summary of duplicate data

Top of Page Top of Page

I want to prevent a field or a set of fields from accepting duplicate values.

Duplication at the table level can occur when one or both of the following rules are ignored:

  • A field must contain unique values.    Examples of such fields are CustomerID, SocialSecurityNumber, and EmailAddress.

To implement this rule, do one of the following:

  • If the table does not have a primary key, assign a field as the table's primary key. Open the table in Design view, click the field name in the top half of the window, and then click Primary Key Button image on the toolbar. A primary key field will only accept unique values and will prompt users if they enter a duplicate value.
  • If the table already has a primary key, set the Indexed property of the field to Yes (No Duplicates) in table Design view. This property prevents the field from accepting the same value for more than one record.
  • The combination of values in two or more fields must be unique for each record.    Examples of such combinations are ContactName, Address, and City in the Contacts table, and Order ID and Product ID in the Order Details table.

Two contacts might have the same name, and two different contacts might share the same address and city, but it is highly unlikely that two contacts with the same name also share the same address and city.

To avoid a combination of field values from being duplicated, create a multiple-field primary key for the table. To create a primary key for the Contacts table based on the ContactName, Address, and City fields, follow these steps:

  1. Open the table in Design view.
  2. Click the gray box to the left of the ContactName column so that the entire row is selected.
  3. Hold down CTRL while you click the grey box to the left of the Address and the City columns.

The rows containing the ContactName, Address, and City fields will be selected.

  1. Click Primary Key Button image on the toolbar.

Because the Indexed property of each individual field is set to Yes (Duplicates OK), you will be able to enter the same name, address, or city in multiple records, but you will not be able to enter the same combination of name, address, and city in more than one record.

Top of Page Top of Page

I want to show all the records from two tables and sort them so that duplicate records are adjacent to each other for easy comparison.

Often, you might end up with two tables that contain duplicate or overlapping data in the same database. For example, consider the Clients and Customers tables in the modified version of the Northwind.mdb sample database.

 Note   Before you continue, you might want to add a Clients table to your sample database. The table should contain the following fields (data type in parentheses): ClientID (AutoNumber), Name (Text), Address (Text), City (Text), Telephone (Text), and FaxNo (Text).

To make it easier to compare the records in the two tables, you might want to keep only 10 records in the Customers table and add 7 records to the Clients table, where 5 records overlap. "Overlap," in this case, means that the records have the same value in the Name field (Clients table) and CustomerName field (Customers table).

The Customers and Clients tables with overlapping data

The two tables contain several common fields, even though the tables contain a different number of fields and some of the matching fields have different names. For example, the Clients table has a Telephone field while the Customers table has a Phone field. Also, the primary key fields of the two tables are different — the Clients table uses an AutoNumber field while the Customers table uses a Text field with a custom format.

Ideally, the data should be in just one table, but you can't solve the problem by simply merging the two tables. For some customers, the Clients table might have the latest information, while for others, the Customers table might have the latest. The best course of action might be to review the contents of both the tables and then decide what to keep and what to delete.

Now you'll create a union query to bring records from two tables together. The query will place duplicate records close together to make it easier to review them.

 Note   You will not be able to edit the field values or delete records when viewing this query in Datasheet view. Union queries are read-only in Datasheet view. To edit or delete data, open the underlying table in Datasheet view.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. Close the Show Table dialog box without adding any tables or queries to the design window.
  4. Click View on the toolbar to switch to SQL view. In the window, paste the following SQL statement.

SELECT [CompanyName], [Address], [City], [Phone], [Fax] FROM [Customers]
UNION ALL SELECT [Name], [Address], [City], [Telephone], [FaxNo] FROM [Clients]
ORDER BY [CompanyName];

The first SELECT statement retrieves records from the Customers table, and the second SELECT statement retrieves records from the Clients table. The UNION ALL clause brings all records from both tables together. The ORDER BY statement sorts the records so that duplicate records are placed together for easy reviewing.

 Note   Each SELECT statement must return the same number of fields, and in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.

  1. Click View Button image to switch to Datasheet view.

Viewing all the records in two tables with overlapping data

The view shows 17 records — all 10 records from the Customers table and all 7 records from the Clients table.

Save the query as Customers and Clients before closing the view. You will be using this query in the next section, View the distinct records from two similar tables and, if a record exists in both, include only one.

Tip    The columns in Datasheet view take their names from the column names in the first table or SELECT statement. If you want to rename a field in the results, use the AS clause to create an alias for the fields, as shown in the following statement.

SELECT [CompanyName] AS [CustomerName], [Phone] AS [Telephone] FROM [Customers]

UNION ALL SELECT [Name] AS [CustomerName], [Telephone] FROM [Clients]

ORDER BY [CustomerName];

Top of Page Top of Page

I want to view the distinct records from two similar tables and, if a record exists in both, include only one.

You want to view the distinct records contained in two similar tables. If a record exists in both tables, include the record that is in the first table, but ignore the corresponding record that is in the second table.

Continuing with the same example used in the previous section, I want to show all the records from two tables and sort them so that duplicate records are adjacent to each other for easy comparison, you'll now learn how to see all the records that are in the Customers table, and only the unique records from the Clients table.

Two records from two different tables will be considered duplicates only if all selected fields match. If you include the CompanyName, City, Address, Phone, and Fax fields in the query, only records that contain matching values in all five fields will be considered duplicates. In this example, you'll include only the CompanyName, City, and Address fields because matching values in these three fields will amount to duplication.

You'll modify the query that you created in the previous section, I want to show all the records from two tables and sort them so that duplicate records are adjacent to each other for easy comparison, to show only distinct records.

 Note   You will not be able to edit the field values or delete records when viewing this query in Datasheet view. Union queries are read-only in Datasheet view. To edit or delete data, open the underlying table in Datasheet view.

  1. In the Database window, under Objects, click Queries, and then double-click the Customers and Clients query.
  2. Click View to see the SQL statement.
  3. Delete the last two fields from both the SELECT statements. Also, delete the word ALL that follows the word UNION. This causes the query to ignore the duplicate records from the Clients table. The SQL statement will look like the following.

SELECT [CompanyName], [Address], [City] FROM [Customers]
UNION SELECT [Name], [Address], [City] FROM [Clients]
ORDER BY [CompanyName];

  1. Click View Button image to switch to Datasheet view.

All records from the Customers table, and only unique records from the Clients table

You will see only 12 records, instead of 17. Note that you won't be able to edit the field values or delete records in this view.

Top of Page Top of Page

I want to view only the duplicate records (those found in both tables) and hide records that are unique to either of two tables.

Another way to review duplicate data in two similar tables is to focus just on the duplicate records — view only records that have a match in the other table, and hide the unique records that are in either table.

You can view just the records that are found in both the tables by creating a simple select query. While you cannot edit fields or delete records when viewing the query results in Datasheet view, you can include fields from both tables and compare field values side by side. The following query shows the customers whose data exists in both the Customers and Clients tables. The Phone and Telephone fields from the two tables help you compare and consolidate conflicting or overlapping data.

Duplicate records with fields from both the tables

Now you'll create the Duplicate Customers query.

 Note   You will not be able to edit the field values or delete records when viewing this query in Datasheet view.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, double-click the Customers table and the Clients table, and then click Close to close the dialog box.
  4. Create a relationship between the two tables so that the query can find matching records. Identify the fields that will contain matching values in duplicate records. In this case, the CompanyName field in the Customers table corresponds to the Name field in the Clients table. Drag the CompanyName field from the first table to the Name field in the second table. The two fields are now connected by a line.

A line between tables indicating that the Customers and Clients tables are related based on the names of the customer

By default, the query creates an inner join between the two tables. An inner join selects only those records where the CompanyName field matches the Name field, which is exactly what you want.

  1. Double-click the CompanyName field to add it to the query design grid. This field will help you identify the records in the Datasheet view.
  2. Double-click the other fields that you want from the Customers and Clients tables to add them to the query design grid. In this case, add the Phone field from the Customers table and the Telephone field from the Clients table.
  3. Click View Button image to switch to Datasheet view. You will see five records, one for each customer that has a record in both tables.

Duplicate records with fields from both the tables

Top of Page Top of Page

I want to view only those records in the first table that don't have a matching record in the second table.

Continuing with the scenario where the Customers and Clients tables have duplicate data, now you'll see what you need to do to view those records in the Customers table that don't have a duplicate record in the Clients table. This might help you make decisions for certain customers based on the contents of the Customers table alone, before you take steps to consolidate the two tables.

You can view just the records in the Customers table that don't have a match in the Clients table by creating a find unmatched query. This query retrieves all the records in the Customers table that don't have a match in the Clients table.

Now you'll create a find unmatched query.

 Note   You can edit the field values or delete records when viewing this query in Datasheet view.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Find Unmatched Query Wizard, and then click OK.

ShowI get the message "The wizard is not installed or has been disabled"

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.


  1. Click the Customers table because it contains the records that you are interested in. Click Next.
  2. Click the Clients table because this is the table that you want to compare against. Click Next.
  3. Click the CompanyName field in the Customers table and the Name field in the Clients table, and then click the <=> button. This will join the tables, based on customer names. Click Next.
  4. Select the fields that you want in your view. In this case, add the CompanyName, Address, City, and Phone fields. Click Next.
  5. Type a name for the query, and click Finish to view the records in Datasheet view. You will see five records. These are the records of the customers in the Customers table that don't have a record in the Clients table.

Customer records that don't have a match in the Clients table

Top of Page Top of Page

I want to delete duplicate records from one table and merge its remaining records into another table

Now you have reviewed the duplicate data that exists in two similar tables, and you want to take action to remove the duplicate data and merge the two tables.

If you need to consolidate various pieces of information that exist in the two tables before you delete some records, you need to manually update the necessary fields in one table before you move onto deleting the matching (or duplicate) records from the other table.

After you have brought the records in one table up-to-date, you can use a delete query to delete the matching records from the second table. Then you can create an append query to add the unique records that are left in the second table to the first table. The last and final step will be to delete the second table.

Assume that the Customers table has the latest information, so you want to delete the matching records from the Clients table. You'll create a delete query to do this.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, double-click the Customers and the Clients tables. Close the dialog box.
  4. Join the two tables by dragging the CompanyName field in the Customers table to the Name field in the Clients table.
  5. Right-click the empty area in the top half of the window, and then click Properties on the shortcut menu. Set the UniqueRecords property to Yes.

Important    Joining the two tables and setting the query's UniqueRecords property to Yes are necessary for avoiding the "Could not delete from the specified tables" message that you will otherwise get when you run the query.

  1. In query Design view, click the arrow next to Query Type on the toolbar, and then click Delete Query in the list.
  2. Drag the asterisk (*) from the Clients table to the Field row of the first column.

The asterisk (*) in the field list represents all the fields in the table.

You will see Clients in the Table row and From in the Delete row. This means the query will delete rows from the Clients table.

  1. Now you need to specify which records must be deleted. You want to delete records where the values in the Name, Address, and City fields of the Clients table match the corresponding fields in the Customers table.

Add the Name, Address, and City fields from the Clients table. Then specify the criteria — type [Customers].[CompanyName], [Customers].[Address], and [Customers].[City] in the Criteria row of the three fields, as shown in the following illustration.

The fields and criteria that determine duplicate records

Records that meet the criteria in the query — those that have matching values in the three fields of each table — will be selected for deletion.

  1. Click View Button image to preview the records that will be deleted when you run the query. You will see the five duplicate records from the Clients table. Save and close the view.

The duplicate records that will be deleted from the Clients table

  1. There is one more step to do before you run the query — reviewing how records in related tables will be affected when the records are deleted from the Clients table. If you run the delete query without doing this step, the query will not be able to delete any records, due to key violations.
  2. Because you eventually plan to delete the Clients table, your goal is to replace the Clients table with the Customers table in all the affected relationships. In the Database window, right-click the window background, and then click Relationships to open the Relationships window. Identify the tables that are related to the Clients table.

Relationships that involve the Clients table

The preceding illustration shows that the Orders table is related to the Clients table.

 Note   In the sample database that you modified, you did not create a relationship between the Clients table and the Orders table. But to follow the remaining steps, assume that there is a relationship between the ClientID field in the Clients table and the CustomerID field in the Orders table, and that both fields share the same data type.

Each record in the Orders table contains the ID of the customer who placed the order. If you delete a customer record from the Clients table, some order records will reference an invalid customer ID. To avoid this, you must identify the relationships between the Clients table and other tables, and in all such relationships, replace the Clients table with the Customers table.

 Note   It might be just a matter of deleting the line connecting the Clients table and the Orders table and then joining the Customers table to the Orders table. But if the primary key fields of the two tables are not the same data type, you will need to edit the design of one of the tables. Do this with care because you can potentially lose data in a field if you change its data type.

  1. After removing all the relationships from the Clients table, close the Relationships window and return to the Database window. Find the delete query, and double-click it. Click Yes twice to confirm that you want to delete records by using the query.

ShowI don't want to see these message boxes each time I run this query

  • On the Tools menu, click Options. Click the Edit/Find tab. Under Confirm, clear the Action queries check box.

  1. If you followed all the preceding steps, and you have permissions to delete records in the database, and the database is not locked or read-only, the delete query will succeed in deleting the duplicate records. Open the Clients table in Datasheet view to verify that it now has only two records.

You have completed the first step, which is to delete duplicate records. Now move on to the second step, which is to create an append query that will copy the remaining records from the Clients table to the Customers table. However, before you create and run the append query, you need to review the fields in the two tables to make sure that the Clients table has all the fields that the Customers table has. If this is not the case, you need to modify the design of the Clients table to add the necessary fields. In the example, the Customers table has a five-letter primary key field that the Clients table does not have. Without this unique key value, you will not be able to add any records to the Customers table.

ShowHow do I add the five-letter CustomerID field to the Clients table?

  1. In the Database window, under Objects, click Tables, and then double-click the Clients table.
  2. Click View to switch to Design view. Right-click the Name row in the top half of the window, and then click Insert Rows on the shortcut menu.
  3. Type NewCustomerID and leave the data type as Text.
  4. On the Table Design toolbar, click Save, and then click View Button image to switch to Datasheet view.
  5. You see a blank NewCustomerID column to the right of the CustomerID column. Enter five-letter IDs for each record in the table. Make sure that the IDs you are specifying are unique and don't already exist in the Customers table.

Populating the new field with 5-letter customer IDs.


Now create an append query to copy the records in the Clients table to the Customers table.

  1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design view, and then click OK.
  3. In the Show Table dialog box, double-click the Clients table. Then click Close.
  4. Click the arrow next to Query Type on the toolbar, and then click Append Query in the list.
  5. In the Append dialog box, click Customers in the Table Name box, and then click Close.
  6. Drag the NewCustomerID field to the grid. Don't add the CustomerID field to the grid because this field does not exist in the Customers table. Add only those fields that have a corresponding field in the Customers table. For each column, set the Append To row to the name of the corresponding field in the Customers table.

The design grid of the append query

  1. Because you want to add all the records in the Clients table to the Customers table, you need not specify any criteria. Click View Button image to preview the records that will be added to the Customers table when you run the query. You will see two client records.

The two records that will be added to the Customers table.

  1. Save and close the view, and return to the Database window.
  2. Find the append query, and double-click it. Click Yes twice to confirm that you want add records by using the query.

ShowI don't want to see these message boxes each time I run this query

  • On the Tools menu, click Options. Click the Edit/Find tab. Under Confirm, clear the Action queries check box.

  1. If you followed all the steps, and the IDs of the records in the Clients table don't already exist in the Customers table, and you have permissions to add records, and the database is not locked or read-only, the append query will succeed in adding the two records to the Customers table. Open the Customers table in Datasheet view to verify that it has 12 records.

The last step is to delete the Clients table.

  1. Make sure that you have permissions to delete tables in the database, that the database is not read-only, and that the table is not in use by another user.
  2. In the Database window, click Tables under Objects, and then click the Clients table.
  3. Press the DELETE key, and then click Yes to confirm that you want to delete the table.

Top of Page Top of Page

I want to see only the distinct records. When viewing data from related tables, all fields in the view match.

Sometimes a query that brings together data from two related tables might show records where all fields match. If you review the tables and find that there aren't actually any duplicate records in the tables, this is why you see duplicate records: The fields that can uniquely identify each record are not included in Datasheet view.

Most tables include one or more fields that can uniquely identify each record. Examples of such fields are CustomerID, EmailAddress, and SocialSecurityNumber. On the other hand, fields such as City and Region cannot uniquely identify a record because multiple customers or employees are likely to live in the same city or region. If your view only includes fields that have the same values for multiple records, you might see records that appear to be duplicates. In such scenarios, don't delete the records, but simply take action to hide duplicate records.

Consider the Order Completion query that includes a handful of fields from the Order and OrderDetails tables, as shown in the following illustration.

The Order Completion query in Datasheet view

You decide that you don't want to see the OrderID, Product, and Value columns in the view, so you delete these fields from the query design grid. But when you switch back to Datasheet view, you see duplicate records. This is because the query continues to show one record in the view for each record in the OrderDetails table, even though your view does not include any fields from that table.

Fields with unique values removed from the Order Completion query

Because an entire order is placed on the same day and shipped on the same day, it is sufficient for you to see just one record per order. In other words, you just want to see one record for each order from the Orders table. To display unique records from the Orders table, open the query in Design view, right-click the gray area to the right of the tables, and then click Properties on the shortcut menu.

Displaying the query's property sheet

In the Query Properties property sheet, set UniqueRecords to Yes. The UniqueRecords property brings unique records from a table based on all the fields in the table. This property affects the query result only when the query is based on more than one table, but the query fields come from a single table.

Switch to Datasheet view, and you will see only one record for each order.

One record per order from the Orders table

But if you scroll down, you might still see duplicate records. In other words, you might find records with matching order and shipped dates.

The view still shows duplicate records

This is because two different orders that were received on the same day were shipped on the same day as well. The UniqueRecords property does not hide such records because the records are not actually duplicates in the underlying table. The OrderID and several other fields don't match for these records, so the query will continue to include them. But if you are only interested in seeing one instance of such records, you need to set the query's UniqueValues property to Yes. The UniqueValues property does not look at the underlying tables for duplicates. It simply looks at the values of the fields that are included in the query, and if all fields match for two records, it hides one from the view.

In the Query Properties property sheet, you will find the UniqueValues property immediately above the UniqueRecords property. Set the UniqueValues property to Yes.

UniqueValues property is set to Yes.

What you see now is not one record per order. Instead, you see just the dates on which one or more orders were received or shipped.

Dates on which orders were received and shipped.

 Note   Both the UniqueRecords and UniqueValues properties cannot be set to Yes. If one is set to Yes, the other will automatically change to No. However, both can be set to No to see all records.

The following table provides a summary to help you choose settings for the UniqueRecords and UniqueValues properties in a query.

If your query is based on: And you want to see: Set: Result:
Two or more tables, but all query fields come from a single table Distinct records from the table (all the fields in the table must match for two records to qualify as duplicates) UniqueRecords to Yes. (UniqueValues will be automatically set to No). If the table contains duplicate records (all fields match), this setting will ignore the duplicates. But the Datasheet view might still show records where all fields match, if the view does not include a field that can uniquely identifies the records that are included in the view.
One, two, or more tables Distinct records in query Datasheet view (all the fields in Datasheet view must match for two records to qualify as duplicates) UniqueValues to Yes. (UniqueRecords will be automatically set to No). You will not see duplicate records (where all fields match) in Datasheet view. But remember that you might not see all the distinct records in the underlying table.
One, two, or more tables All records UniqueValues to No and UniqueRecords to No. You will see all unique and duplicate records.

Top of Page Top of Page

I want to see a specific record from among the duplicates, or summarize duplicate data in related tables.

When viewing data from related tables, you want to see a specific record among the set of duplicates, or summarize duplicate data by using Count, Sum, or another function.

Similar to the scenario described in the previous section, I want to see only the distinct records. When viewing data from related tables, all fields in the view match., you might not actually have duplicate data in the tables, but when you view data from two tables that share a one-to-many relationship, such as the Orders and OrderDetails tables, you might find several records where multiple fields match. For example, an employee might have received multiple orders from the same customer, might have sold the same product as part of different orders on the same day, and so on. If you are interested in finding data to answer queries such as the following, you are dealing with summarizing data, not duplicate data:

  • Add the Quantity column of records where the EmployeeID and ProductID fields match (the total quantity of each product sold by each employee).
  • Count the number of records where the CustomerID and EmployeeID fields match (the total number of orders that each employee handled for each customer).
  • Find the largest orders where the OrderDate and EmployeeID fields match (find the largest order of the day for each employee).

To summarize data, you need to create a totals query similar to the one that you created in a previous section, Select a record from among a set of duplicate records or Count or sum the field values for each set of duplicate records. Those sections describe the procedures for creating a totals query based on a single table. The procedures are only slightly different from those that you will use for creating a totals query based on two related tables. First, remember to add the second table when adding the first table in the Show Table dialog box. Second, when adding the fields that determine duplication, drag the fields that you want from either table, and set the Total row of all such fields to Group By. Third, the field that identifies the record that you want to see or has the values that you want to summarize can come from either table.

Top of Page Top of Page

 
 
Applies to:
Access 2003