Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Unanswered: Distinct Query PROBLEM

    I am needing to do a distinct query on 2 address fields, add1 & add2 only. There are at one address for instance 4 clients registered to that address - hence the distinct query. But if I do a distinct on all fields it will return all 4 clients from that address.

    I then did the below query on the 2 add fields - but cannot figure out how i display a single client name to a unquie address. Any one client name can be used from the address can be used if that helps !

    SELECT DISTINCT tbl_Client_Info.Add1, tbl_Client_Info.Add2
    FROM tbl_Client_Info;

    Any help much appreciated.

    DOnald
    Attached Files Attached Files

  2. #2
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey Donald,

    Why don't you try using the following:

    SELECT DISTINCT Address, First([ClientName]) AS FirstClientName
    FROM ClientTable
    GROUP BY Address;

    Basically SQL gives you them option of grouping a field while still letting you apply one value from another field. In this case it is the word "First" in our statement. It can be substituted for:

    First
    Last
    Max
    Min
    Sum
    Avg
    Count
    etc.........

    First would be the first client name on that address. You can use the Last record or the Max number, Min number as so on.

    Hope this helps.....
    Kal

Posting Permissions

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