Returning Record Counts to a Row

[brumshine]

I'm attempting to return the total number of rows in a query for a year(month >="200801" And <="200812"). Then I'm hoping to return the amount of rows that only meet a certain criteria like month >="200801" And <="200802". I'm using access and have been using the design view thus far. I don't want to return the count inside of a report, I'm hoping to be able to return them to a separate columns in the query.

Thanks in advance for any suggestions/advice you're able to enlighten me with,

BRUMSHINE

 

[jwhite]

Convert your query to a Totals Query (toolbar View - Totals), then in the "Total" row for your field, select "Count". That's the basics. If Year(...) is your criteria, it looks rather strange. If no go, please post your entire SQL statement.

 

[Brianwarnock]

Multiple counts in 1 query is a sum iif situation.
I separate fields of your totals query grid code IIF(criteria,1,0) and select Sum for the Total function.
eg
IIF(Left([monthfield,4)="2008",1,0) will count the year total
IIf(monthfield="200801",1,0) will count January

I hope that you haven't used month as an object name as it is an ACCESS function name.

Brian

 

[jwhite]

Brian, we have no idea what he has already. Giving him alternatives without knowing what he has risks the chance of confusing him.

 

[Brianwarnock]

That sounds like you are telling me off for raining on your parade, but my approach was not an alternative but the only way to go to get multiple counts in one row, which is what he wants.
Count gives the count in a group, but you can only have one group on a row, so count will not work.

Just to explain you give a field a value of 1 or 0 depending on it meeting criteria, then sum the values

CountofJan2008:IIf(monthfield="200801",1,0)
If this is run in a non totals query you will see the list of 1s and 0s then Turn it to a Totals query and Sum them, simple and effective.

Brian

 

[jwhite]

Eh... what? Is everything okay? It's definitely you're parade, and you are welcome to it. Happy New Year!

 

[brumshine]

Thank you both very much for your contributions. I learned something new from each of you. I was able to achieve what I was attempting to do but did it in a different way that described in my first post. I selected "Group By" under Month then constructed the rest of my WHERE statements. The end result was a list of months and the information I requested. I'm very sorry for not being clear with my initial request of what I was trying to return.


Thanks again guys,

BRUMSHINE