Access 2003: text on form but store numerically

[CoachChris]

Let me preface this by saying that I am new to Access and really don't know
my way around it.

I am creating a form that has a combo box for the field 'Risk' in my Access
database that has 3 values: High, Medium and Low. I want to assign each of
these with a numeric value (High=3, Medium=2 and Low=1), which I don't want
the user to see. What I would like to do is when the user selects High have 3
be stored in the database in a separate field called 'RiskValue'. Is this
possible? I think it is and I think it should be relatively simple but I just
can't figure out how to do it in Access. Do I need to bind it?

Ultimately, there will be several different factors in various Value fileds
in the database that I want to sum and store in another field. There will be
2 sums that will correspond with an x and y coordinate that I need to graph
on a quadratic chart or x/y scatter plot. Can I do that in Access too?

Any help, guidance, good reference books, anything, I would greatly
appreciate it!! This task was given to me and I have less than a week to
throw something together. Thanks!

 

[Larry Linson]

I am creating a form that has a combo box for the
field 'Risk' in my Access database that has 3 values:
High, Medium and Low. I want to assign each of
these with a numeric value (High=3, Medium=2 and
Low=1), which I don't want the user to see. What I
would like to do is when the user selects High have 3
be stored in the database in a separate field called
'RiskValue'. Is this possible? I think it is and I think
it should be relatively simple but I just can't figure
out how to do it in Access. Do I need to bind it?

A combo box is bound (its Control Source property) to the Field in the
Form's Record Source where the data is to be stored. It obtains the values
to store and display from a Table, Query, or Value List in its Row Source
property. The Combo Box wizard will offer you the option to hide the
index/key... so if you put the value first, then the description, you can
use either a table with three records, or a value list, and the Wizard will
lead you through the process. But just in case, you simply set the width to
be displayed of that "Bound Column" in the Row Source to zero.

Ultimately, there will be several different factors in
various Value fileds in the database that I want to
sum and store in another field.

If those can be calculated, when needed, from other Fields in the same
Record, then that is what you should do -- do the calculation in a
Calculated Field in the Query you use to retrieve the data, or in a
Calculated Control in the Form or Report in which you present the data.
Otherwise, sooner or later, you'll be staring at a sum that does not equal
all the parts in the same record and wondering "Wha Hoppen?"

There will be 2 sums that will correspond with an
x and y coordinate that I need to graph on a
quadratic chart or x/y scatter plot. Can I do that in
Access too?

Access has a charting capability and I am certain it includes a scatter
plot. I don't know about a "quadratic chart". I don't use the charting
features very often -- perhaps someone else will have better information on
that subject.

Any help, guidance, good reference books, anything, I would greatly
appreciate it!! This task was given to me and I have less than a week to
throw something together. Thanks!

Simple database applications can be "thrown together" very quickly,
sometimes. But few applications of any significant complexity are
"throw-together-in-one-week" projects, regardless of the development tool,
particularly if the devloper is just beginning with the tool. I can give you
tips on some books, but reading/studying them and developing an application
in one week sounds like a "run, don't walk to the nearest exit" situation.

Microsoft Access 2003 Step-by-Step, from Microsoft Press,
is starter material for a true beginner
Microsoft Access 2003 Inside/Out by John Viescas,
also from Microsoft Press, is exceptionally good overall
Access 2002 Developer's Handbook, by Paul Litwin, Ken Getz,
and Mike Gunderloy, published by Sybex
is the advanced developer's must-have (Sybex did not opt to
publish either a 2003 or 2007 edition and we don't know why
not)

Larry Linson
Microsoft Office Access MVP



__________ Information from ESET Smart Security, version of virus signature database 4035 (20090425) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[CoachChris]

Thanks for the info! I have some database experience (oracle) but I have
never used Access and it is tripping me up a bit.

I had set the Risk field in my table to a Value List for High, Medium, Low.
I think I missed a few steps in there when I was setting it up, because I
didn't see the part about the index/key.

I hadn't thought to use a Calculated Query, I was going to try to just use a
SUM in one of the value fields. What you said is better.

I'm sure there is someway I can link it to an x\y scatter plot.

I will get the books you recommended! Thank you very much! I completely
agree that this task is much more involved than my customer believes. I will
try to get more time. Thanks again!