none
count number of unique text strings per group RRS feed

  • 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.
    Wednesday, January 29, 2020 2:13 PM

All replies

  • 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



    Wednesday, January 29, 2020 2:21 PM