Using VBA in Access how can I find the number of records in a table that have a unique field value

You might like to take a look at DatabaseBasics.zip my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file, selecting the following item in the opening form:

    14. Returning a count of distinct values by means of a subquery

opens a form which illustrates how to do this by using a subquery to return distinct values, then counting these in the outer query:

SELECT City, Region, COUNT(*) AS EmployerCount

FROM (SELECT DISTINCT Cities.CityID, City, Region, EmployerID

             FROM Regions INNER JOIN ((Cities INNER JOIN Contacts

             ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers

             ON Contacts.ContactID = ContactEmployers.ContactID)

             ON Regions.RegionID = Cities.RegionID)

GROUP BY City, Region, CityID;

This returns the distinct number of employers of contacts located in each city. In VBA you could call the DLookup function to return an employer count value for any one or more cities.