counting number of each value result from query

[keeling1]

A quick question: I have a query that determines whether a date entered by a user is within certain parameters. I have it set up (using IFF) to return 'True' if it is within the parameters, 'False' if it is not. What I need the query to do is to count the number of Trues and Falses from this record.

My instinct was to use the COUNT function, but this seems only to count the number of records, not to count the number of each value returned in a record. Any help? Thanks in advance.

 

[Scooterbug]

Save the query. Then, create a new query and base the record source off of the saved query. You can then preform a count on the field and set the criteria to equal what you are trying to count. If you need a count of both, put both fields in and set the criteria for one to True and the other to False

 

[keeling1]

Thanks for your response, ScooterBug. Turns out that it's something else that needs counting, not this. So scrap my question. What I actually need the query to do is, for only those records which fall under the date parameters, divide the records into month, count the number of Yeses vs. Nos from another field, and finally plot this on a graph.

I know how to divide the records into months, but I don't know how to get it to do the count by month. Can you help?

 

[Scooterbug]

To setup a query so you can Sum, Average, Count, etc, click on the character on the menu bar that looks like a big E (It's a greek letter...dunno the name of the letter. Holding the mouse over it will display a tag of "Totals"). From there, a new line for the fields will be displayed. You can select Count from there. Once you have the query built, save it.

You can then build another query using the query you saved prior as the source for the field. When you create a query, you get a window that will show you a list of your tables. There is a tab that says Queries. Click on that tab, select the previously created query. Add the field that holds the count and set the criteria there.

Edit:
No need to build two queries. I have done up a quick sample so you can look at it. Basically, there is a IIF statement that checks the difference between DateOne and DateTwo. If the difference is more than 2 days, it returns a False. A True will be returned for date difference less than two days. The query then counts the number of true and falses.

 

[Scooterbug]

Can you post a copy of your database? You can strip out any sensitive data. I dont have Access 2007, so if that is the version you are using, save it as an Access 2000-2003 format. I figure things out much easier if I can actually see what needs to be done

 

[keeling1]

ScooterBug,

Your offer is very kind, and I've attached the file. There's nothing sensitive in it. The two queries I'm working on are called qryGraphAllTags1 and qryGraphAllTags2. It's based on the form frmGraphAllTags.

I think the first query is okay, except for the fact that it doesn't recognize the name of my combobox, and prompts me for criteria instead.

The second query I've just started on. It needs to take only those records which fall under the date parameters, divide the records by month, count the number of Yeses vs. Nos from the Closed? field in the tblMainDataTable, and finally plot these data on a graph.

Help on any part of this would be very much appreciated.