Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Records (2002)

    Having trouble with:

    SELECT DISTINCT tblVendors.CompanyName, [Address] & Chr(13) & Chr(10) & [City] & ", " & [State] & " " & Chr(13) & Chr(10) & [Zip] AS AddressCalcLookup
    FROM tblVendors
    ORDER BY tblVendors.CompanyName;

    I get duplicate Company names.
    Frank

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Unique Records (2002)

    What you may not know is that the DISTINCT applies to all the fields that you have chosen, not just Company name. What you would have to do is delete all fields except Company name to get a distinct list of Companies.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Unique Records (2002)

    If you are getting duplicate CompanyName records, that suggests that the Vendors table has the same CompanyName more than once, with different addresses. If the same company has more than one address, how do you know which address to use? Is there another field in table that can be used to restrict which records are returned by query? Otherwise to get list of unique CompanyNames you'd have to remove calculated address field from query, as previously recommended.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Records (2002)

    The fact that you're getting duplicate company names suggests that you have the same company name entered with different addresses. If you have a table of vendors companies, they should have a unique ID for that company name and the addresses should live in another table. If you want to distinguish between a particular company at one address or another as distinct vendors, then the same company at each address should have a different unique ID, and you *should* get multiple instances of that company name because they are separate vendors.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records (2002)

    Well the truth of it is to simplify things I'm only using tblVendors and not a tblVendors-Contacts linked table.
    We are calling everyone a vendor the company, employees etc. So There is ABC company, Joe; ABC Bill etc.
    All I want in the combo box is ABC or other vendors with contacts come up once and then another combo box to be populated with ABC contacts i.e Joe and Bill etc. . I know this violates Codd's Normalization rules but I need it to work this way.
    Frank

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Records (2002)

    Feel free to violate the rules of normalization, but don't expect Access to behave the way you want it to instead of the way it's designed to work. You'll wind up working 4 times as hard (at least) to make it work (sort of) with denormalized tables, and the reasons for doing so don't usually hold up to scrutiny.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records (2002)

    You are right.
    I added tblVendors-Contacts which links to tblVendors. (one-to-many)
    Will follow the rules.
    Have seen the error or my ways.
    Thanks
    Frank <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  8. #8
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unique Records (2002)

    Actually you just need to add a field to identify what the type of vendor is. In your type field you could have data such as "Company", "Primary Contact", and "Secondary Contact". This way you could include the type in the key and have a field that provides you with an "order by" selection, since you principally want to select the employess of a company ordered by company. This field also gives you an infinite permutation to store your information: you just have to be creative in coming up with type data. This is also a good point for data quality control by using putting the types in a lookup table. This approach works great for phone numbers and addresses. I hate this type of data across multiple tables.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Records (2002)

    Since users should never be allowed to *see* tables and developers can create queries and forms that bring all the data together into what appears to the user to be a single record, there is no particular reason to try to cram everything into one table and a lot of good reasons not to. Frank has just discovered one of the reasons not to.
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records (2002)

    I've seen the light!
    Frank

  11. #11
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unique Records (2002)

    Frank still requires some way to store duplicate records and probably will have to deal with an inner join even if he breaks out this information to a vendor company and a vendor company employee table. Using the phone numbers as an example. Why would one want to have multiple tables storing the various types of phone numbers associated with people or companies? A phone numbe is a phone number is a phone number, they are only differentiated by their relationship to a keyed piece of data. One still has to handle more than one phone number per company and/or person. So why not store all phone numbers in one table and differentiate them by an inherited key and phone number type as a part of the primary key? One table, one index, one place to look, simple sql statement to retrieve and in third normal form. It becomes a simple join to get any phone number.

  12. #12
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unique Records (2002)

    An example query would be:

    SELECT DISTINCT tblVendors.CompanyName, [Company Type] ,[Address] & Chr(13) & Chr(10) & [City] & ", " & [State] & " " & Chr(13) & Chr(10) & [Zip] AS AddressCalcLookup
    FROM tblVendors
    Where [Company Type] = "Company"
    ORDER BY tblVendors.CompanyName;

    To retrieve an individual, say the main sales contact:
    SELECT DISTINCT tblVendors.CompanyName, [Company Type] ,[Address] & Chr(13) & Chr(10) & [City] & ", " & [State] & " " & Chr(13) & Chr(10) & [Zip] AS AddressCalcLookup
    FROM tblVendors
    Where [Company Type] = "Main Sales Contact"
    ORDER BY tblVendors.CompanyName;

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Records (2002)

    <hr> Why would one want to have multiple tables storing the various types of phone numbers associated with people or companies?<hr>
    I have no idea why one woud. It isn't good design to do so.
    Charlotte

Posting Permissions

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