count problem

[danwand]

Hi there,

I have the following query:

SELECT tblUsers.ufName AS [Staff Name], Left([Forename],1) & "." & [surname] AS CKW1, tblUsers_1.ufName AS CKW2, 1 AS intC
FROM ((tblClients INNER JOIN tblCMRKWs ON tblClients.SBNo = tblCMRKWs.SBno) INNER JOIN tblUsers ON tblCMRKWs.KW1ID = tblUsers.userID) INNER JOIN tblUsers AS tblUsers_1 ON tblCMRKWs.KW2ID = tblUsers_1.userID
ORDER BY tblUsers.ufName;

Which returns similar results to below:

STAFF NAME CKW1 CKW2 intC
Clare J.Smith Elaine 1
Clare P.Jones Peter 1
Elaine A.Homes Peter 1
Elaine D.Roberts Clare 1
Elaine I.Hobbs Peter 1
Peter K.Sump Janet 1
Peter A.Smith Elaine 1
Peter A.Jones Clare 1

What i need is another column that counts the number of occurances of each staff name. So the results would be the same above but with an extra column as follows:


STAFF NAME CKW1 CKW2 intC countName
Clare J.Smith Elaine 1 2
Clare P.Jones Peter 1 2
Elaine A.Homes Peter 1 3
Elaine D.Roberts Clare 1 3
Elaine I.Hobbs Peter 1 3
Peter K.Sump Janet 1 3
Peter A.Smith Elaine 1 3
Peter A.Jones Clare 1 3

Is this possible using only 1 query or would i need to use another query or even a subquery?

Any help would be appreciated.

Dan

 

[lespaul]

I think this should work (typed not tested):

SELECT tblUsers.ufName AS [Staff Name], Left([Forename],1) & "." & [surname] AS CKW1, tblUsers_1.ufName AS CKW2, 1 AS intC, count(tblUsers.ufName) As CountName
FROM ((tblClients INNER JOIN tblCMRKWs ON tblClients.SBNo = tblCMRKWs.SBno) INNER JOIN tblUsers ON tblCMRKWs.KW1ID = tblUsers.userID) INNER JOIN tblUsers AS tblUsers_1 ON tblCMRKWs.KW2ID = tblUsers_1.userID
GROUP BY tblUsers.ufName, Left([Forename],1) & "." & [surname], tblUsers_1.ufName, 1
ORDER BY tblUsers.ufName;

you may have to remove the '1' in the group by clause.

Leslie

Have you met Hardy Heron?

 

[danwand]

Thanks for the reply lespaul but the results where the same even when removing intC - all CountName column values were '1'.

I have solved the problem but rather awkwardly:

QUERY 1(rptB3F42s1):

SELECT tblUsers.ufName AS [Staff Name], Left([Forename],1) & "." & [surname] AS CKW1, tblUsers_1.ufName AS CKW2, 1 AS intC
FROM ((tblClients INNER JOIN tblCMRKWs ON tblClients.SBNo = tblCMRKWs.SBno) INNER JOIN tblUsers ON tblCMRKWs.KW1ID = tblUsers.userID) INNER JOIN tblUsers AS tblUsers_1 ON tblCMRKWs.KW2ID = tblUsers_1.userID
GROUP BY tblUsers.ufName, Left([Forename],1) & "." & [surname], tblUsers_1.ufName, 1
ORDER BY tblUsers.ufName;

-This displays the results in the same way as i described in the first part of my original post.


QUERY 2 (rptB3F42s2):
SELECT rptB3F42s1.[Staff Name], Count(rptB3F42s1.[Staff Name]) AS CountName
FROM rptB3F42s1
GROUP BY rptB3F42s1.[Staff Name];

-Which gives me the following results:

StaffName CountName
Clare 2
Elaine 3
Peter 3


QUERY 3 (The final query that will the recordsource for the report:
SELECT rptB3F42s1.[Staff Name], rptB3F42s1.CKW1, rptB3F42s1.CKW2, rptB3F42s1.intC, rptB3F42s2.CountName
FROM rptB3F42s1 INNER JOIN rptB3F42s2 ON rptB3F42s1.[Staff Name] = rptB3F42s2.[Staff Name]
ORDER BY rptB3F42s1.[Staff Name] DESC;

Which gives me the exact results that i was looking for in the 2nd part of my original post.

Is this going round the houses or can the same results be achieved using a single query?