Multiple counts between 2 dates in 1 query (1 Viewer)

[Mark0123]

Hi All,

I have a list of dates and I want 1 query which counts the dates between numerous criteria. for example, colum 1; dates between 1/1/14 and 16/02/2014. Colum2 between 17/02/2014 and 15/04/2014.

Sorry if this is very simple.

I have attached a screenshot.

Thanks in advance

 

[plog]

First, you've chosen some poor field names. 'Date' is a reserved word (http://support.microsoft.com/kb/286335) and causes issues when you want to code or write queries. I suggest you rename it by prefixing it with what that date represents (BirthDate, SalesDate, BahmitzvahDate). Also, non-alpha numeric characters (that includes spaces) cause the same issue, I'd remove them from any table and field names.

For your issue, you need to move your criteria from the WHERE clause to a conditional statement in the SELECT clause. If you look at the SQL of your query you have this for the second field in the SELECT clause:

COUNT([Date]) AS [Aut Ht1]

and this will be in your WHERE clause:

[Aut Ht1] Is Between #1/1/2014# AND #2/10/2014#

You would change it to remove that from the WHERE clause and incorporate the criteria you want into the SELECT clause like this:

SUM(IIf([Date] Is Between #1/1/2014# AND #2/10/2014#, 1,0)) AS [Aut Ht1]

 

[Mark0123]

Sorry I do not really understand your response. Could you please simplify (dumb it down) for me?

Sorry.

 

[plog]

In the query you have, delete the 2nd & 3rd fields (the totals), then put this in a blank field area:

AutHT1: IIf([Date] Is Between #1/1/2014# AND #2/10/2014#, 1,0)

Underneath it, in the 'Total' area, change it to Sum. That will give you the result you want for Aut HT1.

 

[Mark0123]

thank you very much