Option Group

[WLMPilot]

I am new to using option groups. I will have several of them that will be a
value of 1-5 and want to total them up in a report.

Do I need a field for each option group?

Are there field names that can be used to make it easier to total, ie grp1,
grp2, etc.?

Thanks,
Les

 

[Jeff Boyce]

You've told us "how", but not "what". "How" depends on "what"!

If you have data with values of 1-5, and "several of them", is there a
chance you are working with something like a rating scale, or a survey? If
so, you may benefit (*and get more use from Access*) from further
normalization.

If you'll provide more specifics, folks here may be able to offer more
specific suggestions...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

I will have several of them that will be a value of 1-5 and want to total
them up in a report.
Reading your statements I am not sure you know how to use Option Groups.
Just how do you plan to 'total' them?
You know that each differnt value is intended to represent different but
similar items/product/responses. An Option Group can be used for Yes-No-N/A-
Unknown. At most I think you would want to 'Count' instead of summing, like
counting how many are Yes or No.
Option Groups are on forms and are identified like this --
[Forms]![YourFormName]![Frame3]

 

[WLMPilot]

I work for an EMS system. The database is being designed to QA patient care
reports. For each criteria being QA'd, there is a rating scale of 1 through
5. From this rating scale, the value of each criteria will be summed for a
grade.

Les

 

[Jeff Boyce]

So what happens when the number of items (criteria) changes? With the
design your earlier post implied, you'd have to modify your table, your
query, your form(s), your report(s), your procedure(s), pretty much
everything!

By using a one-to-many (relational) approach, adding (or removing) a
criterion is a simple edit on a table... that's all!

You might want to take a look at an approach Duane H. put together:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[WLMPilot]

I guess I am not making myself clear enough. Here is the breakdown of the
design.

I have two tables in one database:
1) Employee Table --> Employee Name and ID#. ID# will be used to link the
two tables
2) QA Table: List the criteria and indicate ranking of 1-5 for each
criteria. I am considering using the tab control in this table. On one
table, information pertaining to the patient report must be entered along
with audit date and audit by. Also Employee ID# to link to employee name in
above table. I considered a combo box for the ranking numbers, but figure I
could cut down on mouse movement by just having radio buttons in an option
group to click on. The ranking of 1-5 will mean the same for each criteria,
ex: 1-missing 2-incomplete 3-needs improvement, etc.

I hope this helps explain things better.

Thanks again for taking time to help.

Les

 

[KARL DEWEY]

Speaking as a certified QA (lapsed) your criteria (1-missing 2-incomplete
3-needs improvement, etc.) is very subjective and needs to be objective.

 

[Jeff Boyce]

I'm not yet visualizing how you are connecting the criteria and the
employee. Is the QA table a list of criteria, or a list of the employees'
ratings?

I am still wondering how you deal with adding/removing a criteria ...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

Also, are you saying "This is my data, tell me how to use it as is", or are
you interested in ensuring that your data is structured in a way that Access
can best use it?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Duane Hookom]

You can keep your table structure as a spreadsheet an move it to Excel. Or,
you can normalize it as suggested by Jeff. There are actually two samples at
http://www.rogersaccesslibrary.com/forum/forum4&SID=72d456e8797881cc1a53e22258b26251.html
that you might want to look at. The At Your Survey and similar Employee
Evaluation.

 

[WLMPilot]

Wow, I did not think I was creating something that is now beginning to make
me wonder. I guess I am now beginning to wonder how to set this up to work
appropriately.

I want a one-to-many database with the employee table being the one and the
QA table being many. A Paramedic or EMT will create many patient care
reports (PCR) throughout a year and the term of employment. The PCRs that
will be QA'd will use the Employee ID# to link back to the Employee Table (at
least that is what I hope). I don't know what else to explain that has not
already been explained. I don't remember how many criteria will be
rated/ranked, but the end goal is to total the value for each ranking. If
all were ranked 5 and there were 20 criteria, than the final score would be
100.

I am opened to suggestions for other ways of doing this. If option groups
is not the way, I can create a combo box listing 1-5 if it will total easier.

Les

 

[Duane Hookom]

I don't believe you have provided your table and field names. If you had,
someone could provide a better answer.

Did you even download and look at the normalized survey and employee
evaluation databases?
I would create this solution with tables like:

tblEmployees (one record per employee)
===========
empEmpID autonumber primary key
empFirstName
empLastName
emp...more fields

tblQACriteria (one record per QA Criteria)
===================
qacQACID autonumber primary key
qacTitle values that used to be field names like "prompt",
"efficient", other criteria

tblQAReport (one record per every evaluation event)
=======================
qarQARID autonumber primary key
qarEmpID link to tblEmployee.empEmpID
qarDate date of evalutation
qarEvalEmpID empEmpID of evaluator

tblQAReportCriteria
================
qrcQRCID autonumber primary key
qrcQARID link to tblQAReport.qarQARID
qrcQACID link to tblQACriteria.qacQACID
qrcScore this is where the actual 1-5 will go.

 

[WLMPilot]

I have tried to open the database from the website you linked to but it is an
earlier version (Access 97) and I have 2007. It has something to do with
trying to open and earlier version for the first time and it is a read-only
file.

I really appreciate your effort and time in trying to help me but I am
beginning to wonder why it is becoming so difficult. I am not sure why I
need to list all my field names. All I wanted to do was find a way, if
possible, to use radio buttons in option groups to grade criterias and then
sum those rankings for a total score. If it is not possible to do this, then
I will go a different direction.

Thank you,
Les

 

[WLMPilot]

I was googling Option Group for Access and started wondering if I need to do
an "AfterUpdate" macro to assign the ranking selected to a field that I
define in order to retain the value of the option group. From what I saw,
when a radio button is selected, its associated value is held by the option
group "frame" (I believe was the word I saw.

Is this a way to total the values of the option groups?

Les

 

[John W. Vinson]

I really appreciate your effort and time in trying to help me but I am
beginning to wonder why it is becoming so difficult. I am not sure why I
need to list all my field names. All I wanted to do was find a way, if
possible, to use radio buttons in option groups to grade criterias and then
sum those rankings for a total score. If it is not possible to do this, then
I will go a different direction.

An Option Group control is simply a way to assign a number value to an integer
number field in your table.

The Form's Recordsource must contain a number field for the grade, or ranking,
or whatever number you want to assign.

The radio buttons in the Option Group control can each be assigned a numeric
value. The one that the user chooses will be stored in that table field.

The labels next to the radio buttons can be anything you choose.

Perhaps that's not what you mean by "use radio buttons... to grade
criterias... and then sum those rankings"; I think that's what people are
finding confusing. We'd love to help you achieve your task, but without
knowing how you're storing the data in your table or what calculations you're
trying to do, it's difficult to do so.

 

[WLMPilot]

John,

If I understand what you have explained, that is exactly what I want to do.
However, to try and clarify better, I will start with something I do know
from Excel and you let me know if I am on track or not.

In a userform in Excel, I can have TextBox5. A user inputs number 5 into
this field. At this point, TextBox5 holds a string (text) of 5.

With that same thought, I assume that the option group field label will also
hold the numeric value based on the assigned value of the radio button, just
as Textbox5 holds the value of "5".

After this point, I guess the confusion is created. I am not sure how the
value of each option group needs to be stored/retained. Do I need to assign
it to a numeric field or something else? Anyway, I want to be able to pull
these values out in a report or form and view and generate a total score
based on the some of the value of each option group.

Les

 

[John W. Vinson]

John,

If I understand what you have explained, that is exactly what I want to do.
However, to try and clarify better, I will start with something I do know
from Excel and you let me know if I am on track or not.

In a userform in Excel, I can have TextBox5. A user inputs number 5 into
this field. At this point, TextBox5 holds a string (text) of 5.

With that same thought, I assume that the option group field label will also
hold the numeric value based on the assigned value of the radio button, just
as Textbox5 holds the value of "5".

After this point, I guess the confusion is created. I am not sure how the
value of each option group needs to be stored/retained. Do I need to assign
it to a numeric field or something else? Anyway, I want to be able to pull
these values out in a report or form and view and generate a total score
based on the some of the value of each option group.

Well... for starters, Excel is NOT Access, and Access is not Excel. They are
different; applying Excel syntax and assumptions to an Access database will
work fine sometimes, and other times will get you in no end of trouble!

In Access, there's a much sharper distinction between Tables (the *only* place
where data is stored) and Forms (tools, windows which let you manage the data
in tables). In Excel a spreadsheet serves multiple functions - data entry,
reports, calculations, etc.; this is NOT the case with an Access table. Access
also has strong datatypes, unlike Excel; a spreadsheet cell can indifferently
store a text string, a date, a number, a picture, a formula, etc.; an Access
Table field must store only the specific datatype that its definition permits
(you can't put a text string such as "N/A" into a Number or Date type field,
for example).

A Form is (typically) bound to a table - the table is called the form's
Recordsource. Each Control on the form can be "bound" to a particular field in
the table (you can have unbound controls, but that's not relevant in this
case).

An Option Group is one particular type of control. It's bound to an Integer
Number field in the form's recordsource. The Option Group as a whole has a
numeric value, the predefined value of whichever radio button or checkbox the
user selected. There is no textbox involved. You could of course *ALSO* bind a
Textbox control to the same field in the table, in which case clicking the
radio button whose value is 5 would store an integer number 5 in the table,
and display "5" in the textbox.

It's really important to make a distinction between data *STORAGE* - a text
string, Number, Date/Time, or other value stored in a field in a table - and
data *DISPLAY*. The number 5 can be displayed in a textbox, an option group, a
combo box, etc. but it's still a number in the table.

So you can bind an Option Group (or combo box or listbox or textbox) to a
Number/Long Integer field in your table, so the user can enter a number into
the table using whatever display tool is most convenient; once you have that
number stored in the table, you can use Queries to retrieve it, do
calculations, sums, averages, etc.

 

[WLMPilot]

Thanks John!

I pretty much understand everything you covered. However, I am one of those
who works better by seeing. So, let me give a simple example and see if it
is what you said.

First, let me say this. I created one option group to see how it worked in
setting it up. At the end of the setup, it asked about the field to attach
it to (wording was different). I had already created a numeric field and I
chose that field.

With that said, let's say I have a simple table with person's Lname, Fname.
If I want an option group for Male and Female (radio buttons), do I need to
also add a numeric field to bind to the option group?

If the answer to the above question is "yes", then I also guess must create
a numeric field for each option group I need for my original question I
started with in this forum?

Les

 

[Duane Hookom]

Only numeric fields can be bound to an option group. You could use an option
group for binding to a [Gender] field if you are using numbers like 1 for
Female and 2 for Male.

Getting back to some original posts regarding your table structures, you
should normalize your tables. You have posted about 8 times in this thread
and not once provided any of your 20 "audit/criteria" field names. I'm not
sure why you haven't. It seems are avoiding or ignoring all of the
recommendations from us.

 

[WLMPilot]

I am not ignoring the recommendations. I asked a simple question at the very
beginning and it was not until a recent reply by John that I felt like
somebody was understanding my questions. I do not see why I need to list the
criteria. These are things that the boss wants to evaluate within a patient
care report. But, I will list a few:

Was trip number entered correctly?
Was protocol followed for the type illness/trauma pt had?
Was all Signatures obtained at hospital?
Was insurance information entered?

There are 4 of the criteria that I will display via labelboxes. I want to
have an option group for each, that the boss has defined 1-5.

In my last example concerning Male/Female, I feel that it paints an exact
picture of what I want. I simply want to know HOW to store the value of the
option group that is chosen while in the FORM view where the data is entered
for the purpose of QA. In my database, I will need to sum all values entered
to obtain a score. Likewise, in the Male/Female example, a count could be
made for the total number of 1's and 2's to determine how many Males and
Females were in the database.

Les

Only numeric fields can be bound to an option group. You could use an option
group for binding to a [Gender] field if you are using numbers like 1 for
Female and 2 for Male.

Getting back to some original posts regarding your table structures, you
should normalize your tables. You have posted about 8 times in this thread
and not once provided any of your 20 "audit/criteria" field names. I'm not
sure why you haven't. It seems are avoiding or ignoring all of the
recommendations from us.

--
Duane Hookom
MS Access MVP

Thanks John!

I pretty much understand everything you covered. However, I am one of
those
who works better by seeing. So, let me give a simple example and see if
it
is what you said.

First, let me say this. I created one option group to see how it worked
in
setting it up. At the end of the setup, it asked about the field to
attach
it to (wording was different). I had already created a numeric field and
I
chose that field.

With that said, let's say I have a simple table with person's Lname,
Fname.
If I want an option group for Male and Female (radio buttons), do I need
to
also add a numeric field to bind to the option group?

If the answer to the above question is "yes", then I also guess must
create
a numeric field for each option group I need for my original question I
started with in this forum?

Les