Analysis Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the opportunity to design a new piece of the puzzle for our company.
Here is the application.
It is a monitoring form that has 30 or so different items that the phone
call is rated on. The rankings are 'Yes', 'No' and 'N/A' for around 15-20
skills then it is ranked 0,2,3 or N/A for another group of skills. I am
supposed to be able to total the rankings for each skill, do some
calculations, and spit out a total score for this one phone monitor on a form
as it is being filled in by the supervisor. I thought of using option boxes
but do not know how to total the values. I like the idea of being able to
calculate everything of the form but I am open to other ideas.

Thank you,
 
I can't be sure from your description, but it sounds like you are using a
spreadsheet. If you are using Access, why? What is it about your
requirements that make Access the tool of choice?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff, it is not a spreadsheet but it is similiar. The requirement that makes
Access the tool of choice is that it must fit into the current Access system
that has been built. We currently have a form for the monitoring piece but
management wants to redo it bringing in the N/A feature. You see, if you
call our call center, it really depends on the problem you are experiencing
on how the call progresses. Management did not want to 'grade to agent' on
something that was not applicable to that specific call.

With that said, What are my options? As I said, I would like to simply sum
the values of the option boxes but I have not been able to do that. Plus it
has the drawback of one table with over 30 elements. Not pretty!
 
I thought it sounded like a spreadsheet!

A lot of folks get into using Access after starting out with Excel.
Unfortunately, what works for them in Excel typically works AGAINST them in
Access.

To get the best use of Access' features and functions, you'll want to read
up on "normalization". In the long run, I suspect you'll be better off if
you design well-normalized tables in Access, link to the basic (Excel-like)
data, and "parse" the data from an Excel structure into your permanent
Access tables.

Access can do calculations, but expects to find data running vertically,
across records, rather than, as in Excel, horizontally, across fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hummm. Since I have to have this up and running by the end of the week, I
will just ask if I can count / sum the values of option boxes? I do not
think so but thought I would ask the expert.
 
If by option boxes you mean option groups, typically with radio buttons,
allowing only one of a set to be chosen, yes, you can "add/sum/count" them.
However (a big "but"), the choices in option groups are numbered, and by
default, are unlikely to be the same value as the text of your option
choices.

If by option boxes you mean check boxes, typically one, two and many of
which can be simultaneously checked, the yes, you can count/add/sum them.
And the news is better here -- each unchecked box is stored as a zero, while
each checked box is stored as a -1 in Access/JET, 1 in SQL Server, ...

You can add up all the checkboxes (and, if necessary, handle the minus
sign).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

I'm sorry; I meant Option Groups where the options are Yes, No, and Not
Applicable. Their values that I assigned are 1,2, and 3. How can I count
all the yes boxes that are clicked; then count the no boxes and subtract the
two to determine a total?

I also have Option Groups that will have values of '0', '2', '3' and N/A for
each element in a category. In these groups I would like to add the values
of each element to assertain the total for that category.

I have maybe 4-5 elements per category and 10 -12 categories. These are all
on a form for inputting by the supervisor. At the bottom of the form, I go
through some calculations to show how the agent did on the call via
displaying a total.

Ideas?
 
One way I know of to handle choices whose values are '0', '1', '2', '3', ...
(i.e., option group selections) is to construct a series of If...Then
statements that translate choices into values which you then could add. I'm
pretty sure there's also a built-in function that could do this, but I
haven't used it.

Of course, if your data was well normalized, it would be much easier to use
Access to do this (hint, hint)!

Another approach might be to export the values to Excel and use Excel
functions, which might be better suited to how it sounds like your data is
organized...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for the help, Jeff. I appreciate your knowledge and time. As normal,
we never have time to do it right but always have time to re-do it.

Quality, Time, Cost - pick two

Kent
 
My mantra is:
Good, fast, cheap -- pick two -- no, wait, it has to be good, pick one!

Jeff
 
Back
Top