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...
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;