need unique record count in query

[Randy]

I have a table. userid, role, inheritance.
so for example userid Admin has 572 rows, but only 26 roles. Each role has 22 inheritances. I want a total by userid with a count of unique roles. so in this example 26 not 572

I tried this and received my count of 572

SELECT DISTINCTROW Left([userid],InStr([userid],'@')-1) AS UserName, tbldata20161122.userid, Count(tbldata20161122.Role) AS CountOfRole
FROM tbldata20161122
GROUP BY Left([userid],InStr([userid],'@')-1), tbldata20161122.userid;

I modified it to this using Dcount and I get an error message

SELECT DISTINCTROW Left([userid],InStr([userid],'@')-1) AS UserName, tbldata20161122.userid, DCount([tbldata20161122.Role],[tbldata20161122]) AS CountOfRole
FROM tbldata20161122
GROUP BY Left([userid],InStr([userid],'@')-1), tbldata20161122.userid;



error message
You tried to execute a query that does not include the specified expression DCount([tbldata20161122.Role],[tbldata20161122]) as part of an aggregate function

 

[Ranman256]

Make a query using your query to get the unique count.
Then do the Dlookup on this new query.

 

[Randy]

not sure I understand
the first query returns

admin 572

I need it to return

admin 26

doing a query on the first query, there is no way I will get 26. Or I am missing something.

 

[Randy]

never mind figured out what you were saying. so yes that worked. Kind of weird having to do 2 queries, but I got the correct count.