COUNT help with JOIN

Hi! I need a little help here. My set up:
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.
In other words, if the recordset (ungrouped) looked like this:
PersonID, ActionID, TypeID
1 10 33
1 10 35
1 10 37
1 11 40
1 11 43
I need the resultset to be:
PersonID ActionID
1 2
Thanks for the help..
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Use COUNT(DISTINCT UserAction.ActionID)
<anonymous@.devdex.com> wrote in message
news:%23nKGriAdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Hi! I need a little help here. My set up:
> 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.
> In other words, if the recordset (ungrouped) looked like this:
> PersonID, ActionID, TypeID
> 1 10 33
> 1 10 35
> 1 10 37
> 1 11 40
> 1 11 43
>
> I need the resultset to be:
> PersonID ActionID
> 1 2
>
> Thanks for the help..
>
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||mistakenly posted this in the wrong NG... this is an Access query and I cant use that syntax -I tried..
"Adam Machanic" wrote:

> Use COUNT(DISTINCT UserAction.ActionID)
>
> <anonymous@.devdex.com> wrote in message
> news:%23nKGriAdEHA.3020@.TK2MSFTNGP11.phx.gbl...
>
>