Query for unique names
Results 1 to 2 of 2

Thread: Query for unique names

  1. #1
    Join Date
    Aug 2006
    Posts
    9

    Query for unique names

    Hi all,
    I am creating a new ACCESSDB. I have 2 tables; 1 table has 12,758 records and the other has 780. I have linked the 2 by the PLANTID field.
    Now, I'm trying to query for the unique company names that owns several power units.
    Here is my SQL CODE:

    SELECT DISTINCT GeneratorAnnual.CECPlantID, GeneratorAnnual.Unit, PowerPlant.CompanyName
    FROM PowerPlant INNER JOIN GeneratorAnnual ON PowerPlant.CECPlantID = GeneratorAnnual.CECPlantID;

    The result shows duplicate owners. I need to get the unique owners. There should be close to 700.

    I'm thinking I did the relationship incorrectly. It shold be a many-to-one, but I dunno...

    TIA

  2. #2
    Join Date
    May 2006
    Posts
    400
    This SQL should give you a list of the various owners (I assume that would be CompanyName) and the count of the units each company owns.


    SELECT DISTINCT PowerPlant.CompanyName, Count(GeneratorAnnual.Unit) AS CountOfUnit
    FROM PowerPlant INNER JOIN GeneratorAnnual ON PowerPlant.CECPlantID = GeneratorAnnual.CECPlantID
    GROUP BY PowerPlant.CompanyName;

Posting Permissions

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