Count all the 9’s in field1

[Dave Smith]

I’m working on a query that will count all the 9,8,7 that fall between the expiration date

Count all the 9’s in field1
Count all the 8’s in field1
Count all the 7’s in field1
Count all the 6’s in field1

That fall between #01/01/10# and #01/31/10# “Jan”
That fall between #02/01/10# and #02/31/10# “Feb”
That fall between #03/01/10# and #03/31/10# “March”

I think it would look something like this, but I cant get it to work:

JanApps: Sum(IIf([Expiration_date] Between #1/1/2010# And #1/31/2010#,Count[field1]=9,0))

So it would look something like this

Jan/Feb/Mar
5 / 8/ 10


Thanks for taking the time to help me learn

[Rabbit]

Can't you do a count, grouping by month, year, and field1? And to get it into that particular layout, you could use a crosstab.

[Dave Smith]

I tried using the crosstab query, but I was having trouble separating 2010.2009,2008 ect

any advice?

[Rabbit]

Filter for the year if you only want a certain year, group by the year if you want all years but you want them separated out.