You are currently offline, waiting for your internet to reconnect

How to use a query to filter unique data in Access

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q292634
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb or .accdb).

For a Microsoft Access 2000 version of this article, see 209793.
For a Microsoft Access 97 version of this article, see 90809.
Summary
When you filter a table to eliminate duplicate data in Microsoft Access 2002 or Microsoft Office Access 2003, use a query that uses one of the aggregate (totals) functions, such as First(), Last(), Min(), or Max(), in the fields that do not contain duplicate data. In the fields that do contain duplicate data, use the GroupBy() function.
More information
For example, suppose you import an inventory table from an application and discover that the data has duplicates in it. To get the data back down to a baseline, such as one record per product, so that you can then re-inventory and have a correct and complete set of data, use a query to filter the data.

Suppose the table looks as follows:
   ProdID   Description   Cost   MarkUp    Quantity   ------------------------------------------------   1        A Product     $1.50  0.5      10   2        B Product     $2.50  0.7      100   3        C Product     $1.59  0.9      25   2        D Product     $4.59  0.8      30   5        E Product     $1.99  0.7      40   6        F Product     $2.69  0.4      60   9        G Product     $4.95  0.8      20   8        H Product     $6.79  0.9      32   9        I Product     $6.89  0.7      0   1        J Product     $2.99  0.5      11				
If you want to filter the table so that it has a unique ProdID code and the first entry from each of the other fields, do the following:
  1. Create a new query that is based on the original table.
  2. Add all the fields from the field list to the query design grid.
  3. In Microsoft Office Access 2003 or in earlier versions of Access, click Totals on the View menu.

    In Microsoft Office Access 2007, click Totals in the Show/Hide group on the Design tab.
  4. Set the Total row of the query design grid to First for every field except ProdID. Set ProdID to Group By.
  5. In Access 2003 or in earlier versions of Access, click Datasheet View on the View menu.

    In Access 2007, click Datasheet View in the View list in the Results group on the Design tab.
The data that you see is a list of unique ProdID data with the first value that is encountered for that product in each of the other fields. If you use this procedure on the sample table, your result is as follows:
   ProdID   Description   Cost   MarkUp   Quantity   -----------------------------------------------   1        A Product     $1.50  0.5      10   2        B Product     $2.50  0.7      100   3        C Product     $1.59  0.9      25   5        E Product     $1.99  0.7      40   6        F Product     $2.69  0.4      60   8        H Product     $6.79  0.9      32   9        G Product     $4.95  0.8      20				
To obtain different results, use the Max(), Min(), or Last() function instead of the First function().

To generate a unique table from this query, change the query type to a make-table query in Design view of the query.

NOTE: This query returns unique data. To find duplicate records and to edit the records, or to choose which records to keep, use the Find Duplicates Query Wizard.
OfficeKBHowTo delete ACC2002 ACC2003 ACC2007
Properties

Article ID: 292634 - Last Review: 07/12/2013 07:25:00 - Revision: 6.4

Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbhowto KB292634
Feedback
tml>=1&t=">