Totals between dates

[kp1279]

I have a database, with a number of users who input direct there reports.

What I have set up is a simple sheet which brings me back the list of users, and there total number of reports.

I have done this with a simple count query.

This works fine.

However, when I try to ammend the query and have it return the same result but within a certain time frame, it throws it all out.

What is does is returns all the users, and count the total ammounts per day, but doesnt then add them together.

So if I went from 01/01/05 to 10/01/05 and the user Smith in that time did 20 records, it would return it on how many per day within that time frame:-

smith 2
smith 5
smith 3

How do I now get it to count the results and club them together?

I have two main colums in the query

First is the user, and group by
second is user and count

but how do I put in a date prompt date range because when I put in date and Between [a] and , it works but doesnt count the user together, it splits them as above.

 

[LittleSmudge]

Post the SQL string for the one that DOES work

and then Post the SQL string for the one that does not work and we'll point you in the right direction to get your thinking right in the future.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[kp1279]

This works:-

SELECT [intel logs].Officer, Count([intel logs].Officer) AS CountOfOfficer, [total no of logs].CountOflogurn
FROM [intel logs], [total no of logs]
GROUP BY [intel logs].Officer, [total no of logs].CountOflogurn
ORDER BY [intel logs].Officer;



This is the date between search, which works but doesn't clump together users: -

SELECT [intel logs].Officer, Count([intel logs].Officer) AS CountOfOfficer, [total no of logs].CountOflogurn, [intel logs].date
FROM [intel logs], [total no of logs]
GROUP BY [intel logs].Officer, [total no of logs].CountOflogurn, [intel logs].date
HAVING ((([intel logs].date) Between [A] And ))
ORDER BY [intel logs].Officer;


Thanks for the help.

 

[PHV]

And this ?
SELECT [intel logs].Officer, Count([intel logs].Officer) AS CountOfOfficer, [total no of logs].CountOflogurn
FROM [intel logs], [total no of logs]
WHERE [intel logs].date Between [A] And
GROUP BY [intel logs].Officer, [total no of logs].CountOflogurn
ORDER BY [intel logs].Officer;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Because you put the [intel logs].date in the Group By clause it will break the result down by date. It is doing what you've told it to do!

Extract the [intel logs].date from the Group By clause.


Then take a look at
Avoid space characters in any 'Name' - Why ? faq700-2190


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[kp1279]

Thanks for all your helps

it now works.

KP