Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Access query for unique records

    I am relatively new to Access and am not getting the results I expected by using "Uniquerecords". I have a table of 172,000 records most of which are duplicate records; when making a "make table" query I select "yes" in the properties window for Uniquerecords and nothing happens with the data in Data View. What am I doing wrong? Thank you, Stephen

  2. #2
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    First, lets answer these questions:

    1) Do I need to make a new table with unique records?

    or

    2) Do I need a query that will display unique records?

    Here is a solution for both using the table 't1' as described below:

    ID f1
    1 red
    2 red
    3 green
    4 blue
    5 yellow
    6 green
    7 yellow
    8 yellow
    9 yellow

    In this example, we have a data set with repetitive color names in the 'f1' field.

    1) Make table with unique records: We want to make a new table ('t1_new’) with unique or distinct color names.

    Here is one way to do that...

    SELECT t1.f1 INTO t1_new
    FROM t1
    GROUP BY t1.f1
    ORDER BY t1.f1;

    Using group by on field 'f1' gives us a distinct list of all color names. You could generalize this technique and include more non-unique fields as well (f2, f2, etc...).


    2) Make a query with unique records: We want to make a query that will display unique or distinct color names.

    Here is one way to do that...

    SELECT t1.f1, Min(t1.ID) AS min_ID, Max(t1.ID) AS max_ID, Count(*) AS rec_count
    FROM t1
    GROUP BY t1.f1
    ORDER BY t1.f1;

    Using group by on field 'f1' gives us a distinct list of all color names. You could generalize this technique and include more non-unique fields as well (f2, f2, etc...).

    Also, by using the Min and Max functions of the ID field, you can find the first and last ID of each color name. The Count(*) expression tells you a record count for each color name.

    It sounds like you are using the query wizard mostly at this point. That is a great way to get things started, but do not be afraid to ad-hoc your SQL and to look at the SQL that the wizard is building for you! To do this, from the query window select 'SQL View'.

    You can achieve the same SQL strings that I have shown you above using only the wizard.

    To create a make table query, select 'Make-Table Query' from the 'Query Type' Drop down list.

    To use the 'Group By' expression, click on the 'Totals' button and select 'Group By' from the 'Total:' line. Min, Max, and Count are also there.

    Hope this helps

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT DISTINCT *
    FROM yourTable
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •