ACC: How to Use a Query to Filter Unique DataLast reviewed: April 23, 1997Article ID: Q90809 |
The information in this article applies to:
SUMMARYNovice: Requires knowledge of the user interface on single-user computers. 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.
MORE INFORMATIONFor 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 11If 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:
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 20To 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:
ARTICLE-ID: Q98230 TITLE : ACC1x: How to Query for Duplicate Records REFERENCES:For more information about using make-table queries, type "make table" in the Office Assistant, click Search, and then click to view "Create a new table from the results of a query with a make-table query."
|
Additional query words: delete
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |