Count # of X per Y (Access2002; WinXP)

  • Thread starter Thread starter bdavids3
  • Start date Start date
B

bdavids3

I have a query to pull about 25 different columns, including Project Manager
(13th column) and Project Name (4th column).

I'd like to add a column to count the number of projects per Project Manager.
Something like a count if, but I'm not really sure how this works in Access.
 
You can use a correlated subquery, e.g. in a simplified form of you query:

SELECT
ProjectID,
ProjectManager,
ProjectName,
(SELECT COUNT (*)
FROM Projects AS P2
WHERE P2.ProjectManager = P1.ProjectManager) AS ProjectCount
FROM Projects AS P1;

This query will not be updatable, however. For an updatable query you can
use the VBA DCount function:

SELECT
ProjectID,
ProjectManager,
ProjectName,
DCOUNT("*","Projects","ProjectManager = """ & [ProjectManager] & """")
AS ProjectCount
FROM Projects;

Ken Sheridan
Stafford, England
 
Back
Top