Results 1 to 15 of 15
  1. #1
    Join Date
    May 2009
    Location
    Nr Ottawa, Canada
    Posts
    6

    Unanswered: DISTINCT driving me nuts (because I'm a newbie)

    I have a database of memberships to an association. I want to create a query to make a mailing list (print off labels), but I only want to return one record per street address. The problem is I have some whole families registered so in some cases there are 7 different records with the same address.
    The following is what I have been trying to accomplish, but am just becoming increasingly frustrated
    SELECT (DISTINCT Members.HomeAddress as Address), Members.FirstName, Members.LastName, Members.HomeCity, Members.HomePostalCode, Members.HomeStateOrProvince, Members.HomeCountry
    FROM Members
    ORDER BY Members.LastName, Members.FirstName, Address;
    Any help in accomplishing this would really be appreciated.
    Thanks.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    SQL isn't my long suit, but I've never seen this before

    (DISTINCT Members.HomeAddress as Address)

    Try losing the parens, using simply

    DISTINCT Members.HomeAddress as Address
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DISTINCT is not a function

    it does not apply to a single column -- rather, it applies to all columns in the SELECT clause

    if there are 7 rows with the same address, but something different in the other columns, then your DISTINCT query will return all of them

    you need to re-think what it is you really want the query to do

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Who is the label going to be addressed to?

    As soon as you bring an adressee into the equation, you are going to return all members unless you use restrict with say first person returned at that address (do you have any way of identifying a 'main' / 'senior' person at an address?).

    That being said, sounds like you have addresses stored in same table as people?

    No need - have an address ID in the members table and address in addresses table.

    Unique label would just be a query on addresses - If you wanted an addressee on the label you would either have to identify main / senior person or just return 'first' member @ that address.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    May 2009
    Location
    Nr Ottawa, Canada
    Posts
    6
    Thanks for your responses.
    r937 - Thanks for pointing out the fact that I cannot specify DISTINCT for one field. I'm not sure quite how to go about "rethinking what I want to do". I know that something is probably not set up properly in the db, but I just want to produce a list of labels to send one letter to each household.
    I am asking how I could create a list of people's (it doesn't really matter if it's addressed to any particular member of the household, because I don't have or want the information regarding whether it's Mom, Dad or brother....) addresses.
    The question is really; is it possible to extract a DISTINCT record for each address? I know that there are 317 duplicate addresses; so if I could avoid printing off 317 labels and mailing them out that would be wonderful.

    I don't know if anyone here has spent hours running in circles and going nowhere, knowing all along that there must be a way, BUT

    EDIT:
    garethdart Sorry, I had prepared the previous response before I got sidetracked for a couple of hours (Saturday evening supper and all).
    Yes unfortunately all the personal information is in one table, so the whole family's info shows up. How would I return the "first" member @ that address?
    I just want to make simple mailout labels and I didn't think that I would be tearing my hair out
    Last edited by JoHerring; 05-16-09 at 23:52.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JoHerring
    I'm not sure quite how to go about "rethinking what I want to do".
    okay, suppose you have three people at the same address, and you say you want only one result per address

    so, the "re-thinking" that is required is: how do you decide which person to pick for the address?

    the tough part about this question is, you have to answer it based on the value of some column(s)

    do you want the person with the latest registration date? the one with the shortest firstname? the one with no middle initial? the one with the highest phone number?

    my advice is, go ahead and print all the labels, and then throw away the ones you don't want to use

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2009
    Location
    Nr Ottawa, Canada
    Posts
    6
    r937 - Thanks again for your input, BUT (sorry) it really doesn't matter to me that much whether I send it to the "Head" of the household or the baby, or the newest or oldest, I'm just trying to narrow it down to one label per address and am wondering if that is possible with an SQL query. I don't really want to throw away 317 labels, which happens to be the number of duplicate addresses.
    So let's narrow it down to - I want to print labels for all members households, but I only want one label per address, and let's say it's the first record with that address (I really don't give a &#@!$& if it's Dad, Mom or Baby) if that makes it easier.
    Is it doable?
    I do really appreciate any input, even if I sound and look frustrated.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT Members.HomeAddress as Address
         , MAX(Members.FirstName) AS First_Name
         , MIN(Members.LastName)  AS Last_Name
         , Members.HomeCity
         , Members.HomePostalCode
         , Members.HomeStateOrProvince
         , Members.HomeCountry
      FROM Members
    GROUP
        BY Members.HomeAddress as Address
         , Members.HomeCity
         , Members.HomePostalCode
         , Members.HomeStateOrProvince
         , Members.HomeCountry
    ORDER 
        BY Last_Name
         , First_Name
         , Address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2009
    Location
    Nr Ottawa, Canada
    Posts
    6
    Thank you for your help. That did the trick after I changed Address to something else. I guess ADD is a reserved word so I was getting a syntax error until I changed it.

  10. #10
    Join Date
    May 2009
    Location
    Nr Ottawa, Canada
    Posts
    6
    Actually that didn't quite work as well as I first thought because the first part of the statement was mixing up names where there are multiple names associated with an address (ie maiden names, step-parents...) so in some cases I would end up with totally invalid names. But the following seems to work. Thanks again for getting me on the right track.
    SELECT FIRST(Members.FirstName) AS First_Name, FIRST(Members.LastName) AS Last_Name, Members.HomeAddress, Members.HomeCity, Members.HomePostalCode, Members.HomeStateOrProvince, Members.HomeCountry
    FROM Members
    WHERE (((Members.HomeAddress) Is Not Null) AND ((Members.HomeEmail) Is Null) AND ((Members.EmailName) Is Null) AND ((Members.Deceased) Is Null))
    GROUP BY Members.HomeAddress, Members.HomeCity, Members.HomePostalCode, Members.HomeStateOrProvince, Members.HomeCountry
    ORDER BY Last_Name, First_Name, Members.HomeAddress;

  11. #11
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Make an address query which finds unique addresses only.

    The use this query linked to the members and return the first member at that address.

    As I said earlier, these problems are due to an issue with your 'normailisation' i.e. Addresses should be stored in a seperate table and linked to the people by address ID. That being said, the above will give you a work-around.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  12. #12
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Can't you just use Distinct but not select the First or Last name? Something like

    Code:
    SELECT Distinct Members.HomeAddress, Members.HomeCity, Members.HomePostalCode, Members.HomeStateOrProvince, Members.HomeCountry
    FROM Members
    Blah Blah
    Then you could just add the old "Valued Customer" or similar for the name when printing the label.

    Just a thought.

    C

  13. #13
    Join Date
    May 2009
    Location
    Nr Ottawa, Canada
    Posts
    6
    Canupus - That is an excellent idea, though around here if I used something like "Valued Customer" or "Dear Member" it would most certainly be taken out of context and embellished, satiricized, ridiculed and more. Now that would be good advertising!
    I still think it's an excellent approach given the nature of the DB, getting the right version of "Valued Customer" would be very time consuming and would have to be bilingual at least and could not have any perceived life/political/religious/social/... preferential connotations.
    I could have fun with this.

  14. #14
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    You could do that...

    However as you are storing 'the same' address for each member, if there are any differences in the way the data has been entered, remember that it will interpret these as 'different' addresses.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Use Rudy's query, but replace MIN() and MAX() with FIRST().
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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