Date and count query

[benc]

Hi I would like a query where i would like to see all the records within a certain date range and the do a count on a field. However when i run this query it shows me all the records on each day if there was a record for that day. It would then count how many records were on that day. however i would like to see a total count. for the whole date range. Can anyone help.

 

[Hayley Baxter]

In your query design put between [start date] and [end date] to return the records for the selected dates then count on the field you want to get the values for by using group by and count(*). Count(*) will give you a complete total for all records.

HTH
Hay

 

[benc]

Hi i tried that already but without any luck here is the SQL code of my query so far:

SELECT dbo_tblOpCusts.Country, Count(dbo_tblOpCusts.Country) AS CountOfCountry, dbo_tblOpRotation.ArrDate
FROM dbo_tblOpCusts INNER JOIN dbo_tblOpRotation ON dbo_tblOpCusts.CustID = dbo_tblOpRotation.CustID
GROUP BY dbo_tblOpCusts.Country, dbo_tblOpRotation.ArrDate
HAVING (((dbo_tblOpRotation.ArrDate) Between #7/1/2002# And #7/31/2002#));

Thanks

 

[Pat Hartman]

You need to remove the date from the select fields list and you need to change the having to a where along with several other things:

SELECT dbo_tblOpCusts.Country, Count(*) AS CountOfCountry
FROM dbo_tblOpCusts INNER JOIN dbo_tblOpRotation ON dbo_tblOpCusts.CustID = dbo_tblOpRotation.CustID
WHERE (((dbo_tblOpRotation.ArrDate) Between #7/1/2002# And #7/31/2002#))
GROUP BY dbo_tblOpCusts.Country;

 

[benc]

Superb thank you