Calculations on Option Group

[btcovey]

What I'm trying to do seems like it should be easy, but I've been banging my
head against the wall for 2 days now and still can't figure it out.

I have an option group with 6 selections, stored as 1-6. I want to run a
summary function on these within date parameters and display a separate count
for each of the 6 options. I've tried executing this with a SQL query, a
nested subquery, using an Iif statement on a calcuated control in the form.
Nothing has worked yet.

Does anyone have any suggestions? Any help will be greatly appreciated.

 

[Daryl S]

You can run a sql query to get your results. Use totals and group by the
field that is bound to the option group, and count for the primary key field.
The SQL would look something like this:

SELECT OptGpFld, Count(PKField)
FROM YourTableOrQuery
GROUP BY OptGpFld;

 

[KARL DEWEY]

If you want horizontal display then try this --

SELECT Sum(IIF([OptGpFld] = 1,1,0)) AS Something_1, Sum(IIF([OptGpFld] =
2,1,0)) AS Something_2, Sum(IIF([OptGpFld] = 3,1,0)) AS Something_3,
Sum(IIF([OptGpFld] = 4,1,0)) AS Something_4, Sum(IIF([OptGpFld] = 5,1,0)) AS
Something_5, Sum(IIF([OptGpFld] = 6,1,0)) AS Something_6
FROM YourTableOrQuery;