Calculating Using Option Groups

B

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.
 
J

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
 
K

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top