count number of unique text strings per group

Question

I have hundreds of different military installations and I'm trying to build a query that will tell me how many squadrons are at each installation.

Answer 1

You would need to explain your table structure a little.

That said, let's suppose you have a Squadrons table (ID, Installation, Squadron) then to ge a listing you could do

SELECT Squadrons.Installation, Squadrons.Squadron
FROM Squadrons
GROUP BY Squadrons.Installation, Squadrons.Squadron;

Or

SELECT DISTINCT Squadrons.Installation, Squadrons.Squadron
FROM Squadrons

 

To get a count you could do

SELECT Squadrons.Installation, Count(Squadrons.Squadron) AS CountOfSquadron
FROM (
    SELECT Squadrons.Installation, Squadrons.Squadron
    FROM Squadrons
    GROUP BY Squadrons.Installation, Squadrons.Squadron
)
GROUP BY Squadrons.Installation;

Or

SELECT Squadrons.Installation, Count(Squadrons.Squadron) AS CountOfSquadron
FROM (
    SELECT DISTINCT Squadrons.Installation, Squadrons.Squadron
    FROM Squadrons
)
GROUP BY Squadrons.Installation;

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net