Count a group between dates

[jwlnewsome]

here goes
im trying to count the number in a group between a time period. the query takes a time period from a form runs then reports who and how many heres what i get

SELECT tblEstimate.Client, Count(tblEstimate.Recieved) AS MyCount
FROM tblEstimate
GROUP BY tblEstimate.Client, tblEstimate.Recieved
HAVING (((tblEstimate.Recieved)>=[Forms]![frmEnquireFront]![EqfromDate] And (tblEstimate.Recieved)<=[Forms]![frmEnquireFront]![EnqtoDate]))
ORDER BY tblEstimate.Client;

Client MyCount

Amey Vectra Ltd 1
Amey Vectra Ltd 1
Ashworth Mairs Group 1
Property Consortium 1
Property Consortium 1


heres what im trying to achieve


Client MyCount

Amey Vectra Ltd 2
Ashworth Mairs Group 1
Property Consortium 2

am i going about this the wrong way
is there an easier way
any help would be great

thanks in advance
john

 

[FoFa]

Remove the tblEstimate.Recieved from your GROUP BY

 

[jwlnewsome]

did that but just got an error that tblEstimate.Recieved was not part of the aggregate fuction

john

 

[Jon K]

SELECT [Client], Count([Recieved]) AS MyCount
FROM tblEstimate
WHERE [Recieved]>=[Forms]![frmEnquireFront]![EqfromDate] And [Recieved]<=[Forms]![frmEnquireFront]![EnqtoDate]
GROUP BY [Client];

.

 

[jwlnewsome]

to jon K thankyou alot

this might sound cheeky but i was wondering if you could maybe explain your sql in a little detail as im interested in how it all works, bought the books but that no substitute to the man.
thanks for the code anyway it works atreat

john

humbled.

 

[Jon K]

In a Totals query, a WHERE condition filters the records whereas a HAVING condition filters the groups.


Suppose you have these two records in the table:-
Client | Received
Amey Vectra Ltd | 12/1/2006
Amey Vectra Ltd | 13/1/2006

When you "GROUP BY [Client], [Received]", Access would do the grouping and counting like this:-
Client | Received | Count
Amey Vectra Ltd | 12/1/2006 | 1
Amey Vectra Ltd | 13/1/2006 | 1

and return those groups that fulfil the HAVING condition,
which of course is not the results you intended.


However, when you move the HAVING condition to the WHERE condition and "GROUP BY [Client]", and suppose the two records do fall within the date range to be searched, Access would do the grouping and counting like this:-
Client | Count
Amey Vectra Ltd | 2

which is the result you intended.


When you GROUP BY [Client], the results would already be in ascending order of Client. So you don't need the "ORDER BY Client" in the SQL statement.


Hope this helps to explain it.
.

 

[jwlnewsome]

cheers pal that hit the spot

thanks for your help
john