Calculating Using Option Groups

[btcovey]

This should be easy, but is confounding me. I have a simple option group on a
form, stored as 1-7 in the table. I want to count the number of records for
each option and, then, present this information by each option. I've tried
this by ORDER BY and GROUP BY queries, beginning with a simple SELECT
sub-query; I tried pulling everything into the query, then using a calculated
control to count by each numbered option - all to no avail.

Does anyone have any suggestions?

Thank you.

 

[John Spencer]

SELECT SomeField, Count(SomeField)
FROM SomeTable
GROUP BY Somefield

In Query Design view
== Add your table
== Add your field two times
== Select View: Totals
== Change GROUP BY to COUNT under one of the two incidents of the field

If you have something more complex to do, give us more information on what
else you are attempting at the same time.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

For a horizontal display of the same data try this --
SELECT SomeField, Sum(IIF([OptionField] = 1, 1, 0)) AS CountOfOne,
Sum(IIF([OptionField] = 2, 1, 0)) AS CountOfTwo, Sum(IIF([OptionField] = 3,
1, 0)) AS CountOfThree, Sum(IIF([OptionField] = 4, 1, 0)) AS CountOfFour,
Sum(IIF([OptionField] = 5, 1, 0)) AS CountOfFive, Sum(IIF([OptionField] = 6,
1, 0)) AS CountOfSix, Sum(IIF([OptionField] = 7, 1, 0)) AS CountOfSeven
FROM SomeTable
GROUP BY Somefield;