Distinct Count

[Bopo]

Hey guys

Well I've been playing with various queries for a while, basically I'd like a unique count of some records within a table, below is test data.

EmpNum | FirstName
5 Bob
5 Bob
10 Harry
5 Bob
10 Harry
11 Ben

Result:

EmpNum | QtyCount
5 3
10 2
11 1

I read somewhere that that Access doesn't like distinct and count function within the same query, although the information was from 2001, therefore I don't know if it applies to do, here's one of my queries:

SELECT COUNT(DISTINCT [E.EmpNum]), E.FirstName, E.LastName, E.EMail
FROM TblEmployee AS E 
INNER JOIN TblRsrcEmp AS RSE 
ON E.EmpNum=RSE.EmpNum
WHERE (((RSE.Returned)='No'));

Advice appreciated.

 

[MStef]

Look at "DemoCountBobA2000.mdb" (attachment, zip).
Run Query1, I think it is what you need.

 

[Bopo]

Hi

Thanks for the help, sadly I've already tried the same thing, I always get the following error

'You tried to execute a query that does not include the following expression 'EmpNum' as part of an arrearage function.'

Here's the query after applying the same from the DB above:

SELECT E.EmpNum, E.FirstName, E.LastName, E.EMail, COUNT(E.EmpNum) As CountOfName
FROM TblEmployee AS E
INNER JOIN TblRsrcEmp AS RSE 
ON E.EmpNum=RSE.EmpNum
WHERE (((RSE.Returned)='No'));

I'm guessing it must be something to do with the usage of multiple tables or something

 

[boblarson]

Actually, I believe your query should be thus:

SELECT E.EmpNum, E.FirstName, E.LastName, E.Email, Count(E.EmpNum) AS CountOfEmpNum
FROM TblEmployee AS E INNER JOIN TblRsrcEmp AS RSE ON E.EmpNum = RSE.EmpNum
GROUP BY E.EmpNum, E.FirstName, E.LastName, E.Email, RSE.Returned
HAVING (((RSE.Returned)='No'));