I have a MS Access database full of customer names and addresses. I want to send out a mailing but only one per address. Therefore if there are 3 different customers at the address I want only one row. Here is the basic query I have so far:

Group   Fname   Lname   Address

So my first attempt was:

SELECT DISTINCT Customers.[Address 1]
FROM Customers

This works as I wanted and returns only unique addresses. However I also need the group, fname and lname fields. When I add these in I lose the DISTINCT functionality as often customers in the same household will have different Lname and grouping.

Whats the best way to select the data I need but still use DISTINCT on the [Address 1] field.

  • And you want one random row from the many in the same address? Commented Jun 27, 2012 at 11:23
  • Yes one random row from each address is fine, either that or pick the row with the oldest [Order Date]
    – Alex
    Commented Jun 27, 2012 at 11:31

1 Answer 1

      Customers AS c
      ( SELECT [Address 1], MIN([Order Date]) AS min_od
        FROM Customers
        GROUP BY [Address 1]
      ) AS grp
         ON  grp.[Address 1] = c.[Address 1]
         AND grp.min_od = c.[Order Date] ;

This may still give you two rows for an address, if you have two rows with the same minimum [Order Date] for an address.

  • You could use min(FName + LName) instead of min([Order Date]) (hopefully, FName+LName is unique for one address :-) ) Commented Jun 27, 2012 at 12:59
  • @FrankSchmitt: If uniqueness is required, then he can use MIN(pk) Commented Jun 27, 2012 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.