Count Checkboxes

[Jose SM]

I need to create a report (hold on, I believe I need a
query, so don't say that I posted in the wrong group, )
that contains the following data:
*It will contain data from a date range
*Information will be grouped by employee
*Count of total RO, RA, RE for each employee

RO, RA and RE are checkboxes in the table.

Would somebody have any idea to go about doing this! Any
help is appreciated.

JSM

 

[tina]

try the following

PARAMETERS [Beginning Date?] DateTime, [Ending Date?]
DateTime;
SELECT Table1.Name, Sum(IIf([RO]=-1,1,0)) AS TotalRO, Sum
(IIf([RA]=-1,1,0)) AS TotalRA, Sum(IIf([RE]=-1,1,0)) AS
TotalRE FROM Table1 WHERE (((Table1.Date) Between
[Beginning Date?] And [Ending Date?])) GROUP BY
Table1.Name;

the above assumes you want a total for each person for the
date range as a whole, not for individual dates in the
range. substitue the correct table and field names in your
db, of course.

this code does run, i tested it; but there might be a
better way rather than using the If statements. anyone
else with alernatives?

hth

ps. fyi, you can achieve the same result by using a simple
select query with date parameters and doing the grouping
and math at the report level.

 

[JSM]

Thank you very much. It was of great help!

-----Original Message-----
try the following

PARAMETERS [Beginning Date?] DateTime, [Ending Date?]
DateTime;
SELECT Table1.Name, Sum(IIf([RO]=-1,1,0)) AS TotalRO, Sum
(IIf([RA]=-1,1,0)) AS TotalRA, Sum(IIf([RE]=-1,1,0)) AS
TotalRE FROM Table1 WHERE (((Table1.Date) Between
[Beginning Date?] And [Ending Date?])) GROUP BY
Table1.Name;

the above assumes you want a total for each person for the
date range as a whole, not for individual dates in the
range. substitue the correct table and field names in your
db, of course.

this code does run, i tested it; but there might be a
better way rather than using the If statements. anyone
else with alernatives?

hth

ps. fyi, you can achieve the same result by using a simple
select query with date parameters and doing the grouping
and math at the report level.

-----Original Message-----
I need to create a report (hold on, I believe I need a
query, so don't say that I posted in the wrong group, )
that contains the following data:
*It will contain data from a date range
*Information will be grouped by employee
*Count of total RO, RA, RE for each employee

RO, RA and RE are checkboxes in the table.

Would somebody have any idea to go about doing this! Any
help is appreciated.

JSM
.

.