Counting Checkboxes

[Newbie]

I have a table with the following columns:
EmpName (text), CustAcct (num), DC (checkbox), DCDate (date), IB (checkbox)
and IBDate (date).

I need to know how to produce a report that counts each checkbox within a
date range. I need to tell it to count all checkboxes that are true with a
date within a specifed range. For Example:

CustAcct DC DCDate IB IBDate
123 Yes 6/1/5 Yes 7/5/5
456 Yes 7/1/5 No

If I'm querying July, I need my report to return a count of 1 for DC and 1
for IB. However, since account number 123 has an IB in July, the DC also
shows up for that account and gets counted. How do I tell it to count only
DC's with a date of July?

 

[Duane Hookom]

=Sum( Abs( DC = True AND Month(DCDate) = 7) )
will count the records for July where the DC field value is True.

 

[tina]

you're storing data in field names (DC, IB), which means your table design
is not normalized. it's not clear from the posted info, but i'm guessing
your table is about CustomerAccounts. and DC, and IB are values that
describe Account events. suggest two tables, as

tblCustomerAccounts
CustAcct (primary key)
EmpName

note: if CustAcct is not the primary key field in your table, then make
sure you link the *actual* primary key field to the foreign key field in the
next table.

tblAccountEvents
EventID (pk)
CustAcct (foreign key from tblAccounts)
EventName
EventDate

only the events that actually happened on an account are entered in
tblAccountEvents, as

EventID CustAcct EventName EventDate
1 123 DC 6/1/05
2 123 IB 7/5/05
3 456 DC 7/1/05

you can do a Totals query on the table, with a Group By EventName and Count
of EventID, and a criteria on EventDate. the results of a query for July
will be

EventName CountOfEventID
DC 1
IB 1

hth

 

[Newbie]

Thank you so much. That worked perfect. One other question. Is there a way
to prompt for the user to input the month to be counted?

 

[Duane Hookom]

You can replace the month number with [Enter Month Number].

I actually prefer to allow users to select a value in a control on a form.