Query Question (2000)

Topic

New Reply

I have two fields in a table. One is a date in the form 1/1/03, and one is a Yes/No field. I want to create a query that will tell me how many of the entries in one month (say January) have a corresponding Yes in the second field. In other words, how many of the entries that begin with a “1” in the date field are “Yes” in the “Yes/No” field. Any ideas? Thanks in advance for any help.

Reply | Quote

Replies

Create a query based on the table. Make it into a Totals query by selecting View | Totals.
Add the date field, and change Group By to Where.
Enter the following condition in the Criteria: line under this field:

Between #1/1/03# And #1/31/03#

Add the yes/no field, and change Group By to Where.
Enter Yes in the Criteria: line under this field.
Enter any field in the third column (it may be the date field or the yes/no field, or another field), and change Group By to Count.

Reply | Quote

I did all of that and it doesn’t work…it says the Count is 0 in the query result when it’s not.

Reply | Quote

Could you post a screen shot of the query in design view? Make the columns in the grid in the lower part of the window wide enough to see the entire contents.

Or, if you are willing to let Loungers examine a stripped down version of your database:

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• Attach the zip file to a reply.
  [/list]

Reply | Quote