COUNT help

[Van T. Dinh]

Try:

SELECT P.PersonID, Count(DUA.ActionID)
FROM Person As P
LEFT JOIN
(
SELECT DISTINCT UA.PersonID, UA.ActionID
FROM tblUserAction AS UA) AS DUA
)
ON P.PersonID = DUA.PersonID
GROUP BY P.PersonID

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
2 tables:


Person.PersonID, COUNT(UserAction.ActionID)
FROM Person LEFT JOIN UserAction
ON Person.PersonID = UserAction.PersonID
GROUP BY Person.PersonID


The problem is that the UserAction table may have multiple records for a
PersonID distinguished only by another column, 'TypeID'. So, I need the
COUNT to only count UNIQUE ActionIDs and I can't

use "(DISTINCT UserAction.ActionID)" apparently..

 

[bidllc]

Hi Van and John, both queries work - I can't thank you enough! I was going crazy with this. Many, many thanks!!!