Im trying to write a simple query which counts all the tickets open by each user, and the sums all the time spent.
SELECT [Fault Logs].Branch, [Fault Logs].User, Count([Fault Logs].User) AS CountOfUser, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].FaultLogNo
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/1/2015# And ([Fault Logs: Notes].[Date and time])<=#8/31/2015#))
GROUP BY [Fault Logs].Branch, [Fault Logs].User;
What I don't understand is when I run the query, the count record is returning the wrong count. I cant figure out what its counting, but its not counting the number of tickets open by the user.
The relationships in my tables are:-
Im using Microsoft access, and Ill try and get some test data together if possible, but the current database is 700Mb at the moment, so a bit big :-S
Can anyone easily see the problem easily?
Thanks in advance