locked
query to not show duplicates based on field value RRS feed

  • Question

  • I've done this before but I'll be darn'd if I can remember how I did it this afternoon.

    We have a table of entities (tEntities) with fields.

    id, Name, City, Category

    There are duplicates in the Name field

    We want a query to display all the fields in each record... but only the first occurrence relative to the Name field

    For instance...

    6, Joe's Tires, LA, Tires

    23, Joe's Tires, NY, Tires

    57, Joe's Tires, Dallas, Tires

    76, Mary's Fine Foods, Dining

    We would only want the query to display

    6, Joe's Tires, LA, Tires

    76, Mary's Fine Foods, Dining

    thanks for any help.

    Tuesday, August 25, 2015 8:51 PM

Answers

  • SELECT First(TEntities.[Name]) AS [Name Field], Count(TEntities.[Name]) AS NumberOfDups
    FROM TEntities
    GROUP BY TEntities.[Name]
    HAVING (((Count(TEntities.[Name]))>1));

    OR

    SELECT First(TEntities.Name) AS [Name Field], First(TEntities.City) AS FirstOfCity, First(TEntities.Category) AS FirstOfCategory, Count(TEntities.Name) AS NumberOfDups
    FROM TEntities
    GROUP BY TEntities.Name
    HAVING (((Count(TEntities.Name))>1));

    --------------------------------------------------------------------------------------------------------------

    I apologize, I misread your requirement.  I thought you wanted to find the first record of any duplicates. 

    To find only one record for each name use the following SQL:

    SELECT DISTINCT tEntities.Name, tEntities.Category
    FROM tEntities;

    In this example, you will leave out the ID and the City, as they make the record unique.

    --------------------------------------------------------------------------------------------------------------

    If you want to see the other fields, the following SQL should do the trick:

    SELECT First(tEntities.id) AS FirstOfid, First(tEntities.Name) AS FirstOfName, First(tEntities.City) AS FirstOfCity, tEntities.Category
    FROM tEntities
    GROUP BY tEntities.Category;

    *************************************************************************************************

    CAUTION: Be aware that your one field named "Name" is not permitted by Access.  I expect that you should have gotten a warning about this when you attempted to create the field in your table.  Access will allow you to override this and continue on with the name.  However, you could encounter problems down the road when attempting to reference the field.  "Name" is a reserved word and should not be used for any object or variable in your db. 





    Tuesday, August 25, 2015 9:05 PM
  • Try these two queries --

       qryFirstName --

    SELECT Min(TEntities.ID) AS FirstID, TEntities.Name

    FROM TEntities

    GROUP BY TEntities.Name;

        -----------

    SELECT TEntities.ID , TEntities.Name, TEntities.City, TEntities.Category

     FROM TEntities INNER JOIN qryFirstName ON TEntities.ID = qryFirstName.FirstID;


    Build a little, test a little



    Tuesday, August 25, 2015 9:44 PM

All replies

  • SELECT First(TEntities.[Name]) AS [Name Field], Count(TEntities.[Name]) AS NumberOfDups
    FROM TEntities
    GROUP BY TEntities.[Name]
    HAVING (((Count(TEntities.[Name]))>1));

    OR

    SELECT First(TEntities.Name) AS [Name Field], First(TEntities.City) AS FirstOfCity, First(TEntities.Category) AS FirstOfCategory, Count(TEntities.Name) AS NumberOfDups
    FROM TEntities
    GROUP BY TEntities.Name
    HAVING (((Count(TEntities.Name))>1));

    --------------------------------------------------------------------------------------------------------------

    I apologize, I misread your requirement.  I thought you wanted to find the first record of any duplicates. 

    To find only one record for each name use the following SQL:

    SELECT DISTINCT tEntities.Name, tEntities.Category
    FROM tEntities;

    In this example, you will leave out the ID and the City, as they make the record unique.

    --------------------------------------------------------------------------------------------------------------

    If you want to see the other fields, the following SQL should do the trick:

    SELECT First(tEntities.id) AS FirstOfid, First(tEntities.Name) AS FirstOfName, First(tEntities.City) AS FirstOfCity, tEntities.Category
    FROM tEntities
    GROUP BY tEntities.Category;

    *************************************************************************************************

    CAUTION: Be aware that your one field named "Name" is not permitted by Access.  I expect that you should have gotten a warning about this when you attempted to create the field in your table.  Access will allow you to override this and continue on with the name.  However, you could encounter problems down the road when attempting to reference the field.  "Name" is a reserved word and should not be used for any object or variable in your db. 





    Tuesday, August 25, 2015 9:05 PM
  • Try these two queries --

       qryFirstName --

    SELECT Min(TEntities.ID) AS FirstID, TEntities.Name

    FROM TEntities

    GROUP BY TEntities.Name;

        -----------

    SELECT TEntities.ID , TEntities.Name, TEntities.City, TEntities.Category

     FROM TEntities INNER JOIN qryFirstName ON TEntities.ID = qryFirstName.FirstID;


    Build a little, test a little



    Tuesday, August 25, 2015 9:44 PM
  • select 
        min(id) as minid
        , Name
        , City
        , Category
    FROM
        tEntities
    GROUP BY
        , Name
        , City
        , Category
    


    Michał

    Wednesday, August 26, 2015 6:23 AM