As many have already pointed out, using a separate field (and a separate
option group on form) to handle these evaluation criteria is pretty well
guaranteed to cost you (or someone) an inordinate amount of time doing
"maintenance". If you/your boss should ever decide to add another
criterion (or remove 2), you will need to revisit the table, the form(s),
the queries, ..., etc. and "fix" them.
This is because your data is organized more like what you'd see in a
spreadsheet (?new criterion? add a column!) than in a well-normalized
relational database.
So what? you ask... So, Access' features and functions are optimized to
work with relational data, not spreadsheet data. If you insist on
feeding it 'sheet data, you (and Access) will have to work overtime to
overcome the data structure.
On the other hand, if you used one ROW per evaluation criterion, you
could add/remove criteria by a simple table entry, and the "sum" of the
criteria would be a simple Totals query in Access, rather than the
work-around addition of the multiple columns, each adjusted for potential
nulls.
Pay now or pay later! If you don't normalize your data now, you will be
paying and paying, reworking (i.e. "maintenance") your system every time
there's a change in criteria.
Good luck!
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 said:
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
:
On Thu, 22 Apr 2010 10:49:01 -0700, 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.
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.
