Microsoft KB Archive/90809

= ACC: How to Use a Query to Filter Unique Data =

Article ID: 90809

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q90809



Novice: Requires knowledge of the user interface on single-user computers.



SUMMARY
When you want to filter a table to eliminate duplicate data, you need to 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 the duplicate data. The fields that contain the duplicate data should contain the GroupBy function.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center



MORE INFORMATION
For example, suppose you import an inventory table from an application and discover that the data has duplicates in it. Your goal is 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. You can use a query to filter the data.

Your table might look like this:   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 down to the point where it has a unique ProdID code and take the first entry from each of the other fields, you can create a query to do this, as follows:
 * 1) Create a new query based on the original table.
 * 2) Add all the fields from the field list to the QBE grid.
 * 3) On the View menu, click Totals.
 * 4) Set the Total row of the query grid to First for every field except ProdID. Set ProdID to Group By.
 * 5) On the View menu, click Datasheet View (or Datasheet in Microsoft Access 7.0 or earlier.

The data that you see should be 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 above data, your result is the following:   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, you can use Max, Min, or Last instead of First.

To generate a unique table from this query, you can change the query into a make-table query in Design view of the query.

NOTE: This query will return the unique data. To find duplicate records and edit the records or choose which record to keep, use the Find Duplicates Query Wizard in Microsoft Access 2.0 and later. For more information about finding duplicates using Microsoft Access version 1.x, please see the following article in the Microsoft Knowledge Base:

98230 ACC1x: How to Query for Duplicate Records

