COUNT, GROUP BY query problem

[chris6976]

Hi

I have a table 'Calls' with the following fields:
Call_Ref
Call_Type
Status
User_ID

I wish to produce a query/report that displays the fields, Call_Ref
Call_Type & Status PER User_ID and sums the total of calls, i.e.

User 1
Call Ref Call_Type Status Total Calls/User 1

User 2
Call Ref Call_Type Status Total Calls/User 2

and so on...

I have so far created the following SQL statement, which is close, but does not appear to COUNT and GROUP correctly. Note: the text boxes are 2 dates

----
SELECT [User].[User_ID], Calls.Call_Type, Calls.Status, COUNT ([User].[User_ID])
FROM Calls INNER JOIN [User] ON [Calls].[User_ID]=[User].[User_ID]
WHERE ((([Calls].[Date_Time]) Between [Forms]![frmCallsQuery]![Text1].[Value] And DateAdd("d",1,[Forms]![frmCallsQuery]![Text2].[Value])))
GROUP BY [User].[User_ID], Calls.Call_Type, Calls.Status;
----

Can anyone point me in the right direction, as I feel it is very close to the correct statement, but i just cannot spot the error!

Thanks in advance :s

 

[PaulBricker]

Why are you using two tables? What is the relationship between User and Calls? The problem is probably in your Join. If you turn off the Totals does the query return the records you think it should?

Paul

 

[sedgely]

Chris
The following seems to work for me
---
SELECT Calls.Call_Ref, Calls.Call_Type, Calls.Status, Count(Calls.User_ID) AS CountOfUser_ID, Calls.User_ID
FROM Calls
WHERE (((Calls.Date_Time) Between #1/1/2001# And #1/4/2001#))
GROUP BY Calls.Call_Ref, Calls.Call_Type, Calls.Status, Calls.User_ID;
---
I have typed in the dates but you could but the form text fields in there

HTH
Craig

 

[chris6976]

Craig

This only appears to count each individual "call", i.e. Count(Calls.User_ID) = 1 for each row.

What I am looking for is a sum total for each user.
For example, if User 1 has taken 6 calls, I want the count to say 6.

Does that help a bit?

Cheers

Chris

 

[scriverb]

Just Count the value one(1). You are trying to count the User_ID and there will be one for each user_ID.

SELECT [User].[User_ID], Calls.Call_Type, Calls.Status, COUNT (1) as CountOfRecords
FROM Calls INNER JOIN [User] ON [Calls].[User_ID]=[User].[User_ID]
WHERE ((([Calls].[Date_Time]) Between [Forms]![frmCallsQuery]![Text1].[Value] And DateAdd("d",1,[Forms]![frmCallsQuery]![Text2].[Value])))
GROUP BY [User].[User_ID], Calls.Call_Type, Calls.Status;

Let me know if this gives you what you want.
Bob Scriver