Count by State (1 Viewer)

[BennyLinton]

I need to modify the following query to count the number of people in each State in the US, any clues?:

SELECT dbo_certs.peopleId, dbo_People.FirstName AS [First], dbo_People.LastName AS [Last],
dbo_Status.StatusName AS Status, dbo_PeopleContactInfo.stateAbbrev AS State
FROM dbo_PeopleContactInfo INNER JOIN ((dbo_certs INNER JOIN dbo_People ON
dbo_certs.peopleId = dbo_People.peopleId) INNER JOIN dbo_Status ON dbo_certs.statusID = dbo_Status.StatusId)
ON dbo_PeopleContactInfo.peopleId = dbo_People.peopleId
WHERE (((dbo_certs.statusID)=2) AND ((dbo_PeopleContactInfo.Preferred)=True));

 

[isladogs]

Use an aggregate (totals) query.
Add the fields State and PersonID.
Both will by default show Group By in the Totals row. Change that to Count for the PersonID field.

 

[BennyLinton]

Like putting count() at the beginning of the query and GROUP BY (State) at the end?

 

[isladogs]

Not quite sure what you're asking.
Create a new query based on the query in your first pet.
In the query design ribbon, click the Totals button.
Now do what I said in my previous reply
The query design will look similar to this but with your field names