query to not show duplicates based on field value

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.

Answer 1

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. 

Answer 2

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

Answer 3

select 
    min(id) as minid
    , Name
    , City
    , Category
FROM
    tEntities
GROUP BY
    , Name
    , City
    , Category

---

Michał