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? – ypercubeᵀᴹ Jun 27 '12 at 11:23
  • Yes one random row from each address is fine, either that or pick the row with the oldest [Order Date] – Alex Jun 27 '12 at 11:31
      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 :-) ) – Frank Schmitt Jun 27 '12 at 12:59
  • @FrankSchmitt: If uniqueness is required, then he can use MIN(pk) – ypercubeᵀᴹ Jun 27 '12 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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