Option Group Query

[Guest]

Can anyone tell me how to query a option group.

I have an option group with 3 choices. Yes (1) No (2) Missing (3). The
values are in brackets. The field is a number data type. What I want to be
able to do is make a query that will tell me that how many Yes's, No's and
Missings occured. I know I can do this by using Filter by selection and
counting the records but is their a way of doing it in design view?

Thanks in advance for advice

 

[tina]

do you mean you have an option group control in a *form*? and the control is
bound to a field in the form's underlying table? which stores the value
chosen as a number: 1, 2 or 3? if yes to all, then you can query the table
to get a count of the answers in that field. let's say the table field that
stores the values is called Status. create a Select query based on the
table, and in Design view pull the Status field and the table's primary key
field into the grid. from the toolbar, click the Totals button (looks like a
fancy capital E or a capital M on its' side). GroupBy the Status field, and
Count the primary key field.

hth

 

[Guest]

Thanks Tina that works.

Could you also tell me as you correctly stated the option group is in a
form. I have several of these groups in a form. Is there a way I can do this
for all of them at the same time or can I only query 1 at a time. Ultimately
what I want to achieve is to make a chart probably in excel of the results
from the option groups.

 

[tina]

the form is not part of the equation here. the issue is where and how the
data is stored in the *table*, because you're querying the data in the
table. grouping has a hierarchy from left to right, for example

GROUP BY state, county, city

so within the dataset, the records would be grouped by state, then within
each state grouped by county, then within each county grouped by city.
usually related data has that same sort of hierarchy.

so to try to answer your question, i need to know whether your data is
normalized (stored relationally) or in a more "flat-file" Excel-like format.
please post your table structure, with a bit of explanation of the fields.

hth

 

[Guest]

Hi Tina

Thanks very much for your advice. In answer to your question I have 1 table
which is more the flat-file you discribled and the second which has a
sub-table which is rationalised. (I made the second up through advice given
through this site.) I tried out your advice and it works great for the
second but not for the first so am now wondering if it would be better to set
the first up with a sub-table in the same manner. I know you're probably
shouting "What's stopping you" my concern is that one item on this table has
upto 12 checks and result are recorded in the option group, yes, no and
missing. Potentially there could be hundreds of items so the records in the
checks fields would be even more. How many records does access store?

On the first table I had individually named the different checks and made up
the option group for each one so that when you are filling up the form, it
looks just like the form you're filling in. ie,

tbl
unitno (primary key)
date
time
check1
result1 (option group)
check2
result2(option group)
and so on

I don't know how you would query this to get the same results as I achieved
in the second table. Being new to access and databases I thought that this
would be easiest when filling in the data but the more I learn from this site
I can see that it might be better in the eye but when applying queries it is
not the best. Sorry for the rambling but thought it better to try and
explain what I had done.

Thanks for your help your advice is allowing me to put the info into an
excel chart the way that I want it.

 

[tina]

there is no static limit to the number of records that a table can hold. the
size of a table is limited to the size limit of the database file, because
it's possible to put a single table in a database, and use that table as a
datasource in another database.

having said that, and as far as your query goes, it's up to you. you can
re-organize the data in that table into a relationally sound structure,
which likely will mean two or more tables, and then query it out. or you can
write a bunch of individual queries such as the one i first posted, and dump
the data into Excel in bits and pieces.

i do recommend, though, that you study up on relational design principles -
if not for the sake of this database, then for future ones that you'll
build. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth