duh! what am I forgetting?

[Zorro1265]

I have a table with 6 fields that are check boxes, there is also a date field mixed in with some other things. I want to make a query that will give me a monthly total of each of these check boxes.
Example
-----check1----check2----check3----check4
June 1 5 3 7

That is the result I am looking for so I can see how many times each check box is checked per month. I think I am getting a brain error here. By the way its access 2000.

Zorro

 

[KenG]

I don't understand your example.
-----check1----check2----check3----check4
June 1 5 3 7
What are these numbers if each field is a check box?
Maybe I can help if I understand this better...
Ken

 

[Zorro1265]

Sorry I was fuzzy.

The example shows what I would like to get back from my query. I would like a monthly total of each check box.

In query design I have setup a date column that sorts by month. I have one of the check box feilds in the next 2 columns in query design, one column doing the count and the next column with a where looking for the checkbox to be checked (-1 right?) this works until I add the rest of the checkboxes to the design grid. Then I get nothing. It seems like a simple request to count how many times the boxes are checked in a month so I am sure I am just being dense with this. Zorro

 

[KenG]

Let me see if I got this right.
The query design grid you want to use will have a date column, then 2 columns for each check box field. One the these 2 columns (for the check box field) will have a the criteria row set to -1. This will give you a total of (2*6)+1=13 design grid columns.
If this is correct then your query will only return the records (rows) from your original table where all of the check boxes are checked for each record in the original data table.
I think you need to redisign the data table. I could make a suggestion if I had a little more background on what the check box fields in this table are used for.

 

[Zorro1265]

I will make whatever query you think will work. I have a table with a date field and several (6) check boxes are part of the table. I want to be able to run a query that will count by month how many times each check box is checked off. What would be the best way to do it. Zorro

 

[JoeMiller]

Make a new column that Sums Each checkbox first so you get the aggregate checks for each record like this:

SumRecordChecks: Abs(Nz(Check1)+Nz(Check2)+Nz(Check3)+Nz(Check4)+Nz(Check5)+Nz(Check6))

Now if you want to get the Total SumRecordChecks (ie: the number of times Yes occurs), make a new query that selects just the field SumRecordChecks and make a Total query out of it. Click the Totals icon on the menu bar, and set the total line to Sum.

Hope that helps!
Joe Miller
joe.miller@flotech.net

 

[Zorro1265]

Will this tell me that checkbox 1 was checked 15 times this month and checkbox 2 was checked 7 times etc? My goal is to be able to tell someone how many time each type of checkbox (they represent different tests) was checked. Zorro

 

[JoeMiller]

No, it will not tell you that. If you want that, jump straight to the totals query I talked about. Select Check1 thru Check6 in the qbe, click the totals button, set Sum on all of them and you'll see a negative number representing how many times each one was checked.

If you want to group by month, add your date field in, set it's Total line to Group By and format the date as a month (using the properties of the column). Then you'll get the sum of check 1 through 6 by month.

Joe Miller
joe.miller@flotech.net

 

[Zorro1265]

I can't believe I was that dense. I was trying to get it to count the check boxes not even thinking it could just count up all the -1 values.

Thanks!
Zorro

 

[KenG]

Try this (Paste it in the SQL view of a new query)
SELECT Month([Table1]![Date]) AS Month, Sum(Table1.chk1) AS SumOfchk1, Sum(Table1.chk2) AS SumOfchk2, Sum(Table1.chk3) AS SumOfchk3, Sum(Table1.chk4) AS SumOfchk4, Sum(Table1.chk5) AS SumOfchk5, Sum(Table1.chk6) AS SumOfchk6
FROM Table1
GROUP BY Month([Table1]![Date]);
You might want to modify the Sum function with the Abs() function so the numbers are positive.
Good luck,
Ken

 

[Zorro1265]

Thanks Ken and Joe, I think its time for a vacation I can't beleive I missed that one. Zorro