Option Group from Form into Report (1 Viewer)

[Brian Roberts]

I have created a database that has an option group with five check boxes in a form that is not linked to a table. I would like to do a count on 1 of those check boxes(eg. Check 1)in a query or report. I am having trouble creating an expression for the count since my option group and check boxes are not included in any table. Any suggestions?

 

[Pat Hartman]

There is nothing to count. If the value of the option group is not stored in a table all you would ever have is the value displayed by the currently opened form.

By the way your question is phrased, it sounds like you think that the values for an option group would be stored as 5 fields. That is not how they work. The option group would be bound to 1 field and that field would contain 1 of 5 values. Depending on the value, Access will check the appropriate radio button or checkbox.

 

[Brian Roberts]

Pat, I have over 500 records on this form not attached to the table. Can I create new fields on a table to accomodate results of this option group from this non-table linked form? Or can I get a sum of the various values of the option group on the form?

 

[KevinM]

Your scenario now seems even more confusing than before!

What do you mean you have "500 records on a form that are not attached to a table" !!

How can you have 'records' without a table(s) to store them.

"Can I create new fields on a table to accomodate results of this option group"

You need to re-design your db from ground up.
The starting point being the tables themselves.

If you have just created a form with a load of unbound controls on, then you have gone the wrong way about it.

 

[Brian Roberts]

I will try to clarify things for you. I recently discovered the link between my table and my form. It is called Client.It is a text field on my table and an option group on my form. Now, what I would like to do is create a expression in a query or report that counts one of the values in the option group(Client). Does this alleviate some of the confusion? And more importantly, can anybody help?

 

[Pat Hartman]

You can create a totals query using the QBE grid.
1.Open the new query window.
2.Add the table that contains the control source of the option group.
3.Select the option group field.
4.Press the sigma button to a totals query
5.Add a new column to count the values. Place the following in the field Cell:
CountOfItems:Count(*)
Make sure the total cell is empty.

This query will produce a list of the distinct values for this column with a count of each occurance. for example:

A 45
B 908
C 1

 

[Brian Roberts]

Pat, I tried your suggestion but my counts of the option group values aren't accurate. For example For Option Value 1 on May 01/01 displays 2 for CountOfItems yet there is only one record for Option Value 1 on May 01/01.

 

[Pat Hartman]

The query sample I posted did not include any selection criteris so it is counting the entire domain. Did you include selection criteria in your query? If you do add the date field to the QBE grid, be sure to uncheck the show box or the query will not count properly.