Database Questions

[Bunky]

First off I am fairly new (5-7 months) to Access. I had been involved in
Mainframe applications prior to that for many years.

I was asked to design a form in which would give the client the option to
'grade an agent's phone call'. (I am working in a call center environment.)
I have designed a very nice form that all management loves but . . there is
no good way to access the data once the data has been entered. I am certain
that the table design.database design is probably the problem. On the form,
I have had to make extensive use of the option groups. I might be able to
switch the form to utilize combo boxes but I still think my design is the
problem.

I have a staff table and a monitor table. It is a one to many relationship.
The monitor table has all the values from the form (over 35) stored in it and
each value can be a point value, a yes/no or n/a. I have a lot of hidden
text boxes on the form to determine if each item selected should be included
in the overall totals or not. As I say, the form works perfectly but I
cannot for the life of me figure out how the queries need to be in the
reporting part.

It appears that if I store the totals from the form, my problem is solved
but I know that is a big no-no. But it does look good at the moment.

Any ideas on how I can report on the monitor scores, when the values stored
are yes/no/na?

 

[John W. Vinson]

First off I am fairly new (5-7 months) to Access. I had been involved in
Mainframe applications prior to that for many years.

I was asked to design a form in which would give the client the option to
'grade an agent's phone call'. (I am working in a call center environment.)
I have designed a very nice form that all management loves but . . there is
no good way to access the data once the data has been entered. I am certain
that the table design.database design is probably the problem. On the form,
I have had to make extensive use of the option groups. I might be able to
switch the form to utilize combo boxes but I still think my design is the
problem.

I'm inclined to agree.

I have a staff table and a monitor table. It is a one to many relationship.
The monitor table has all the values from the form (over 35) stored in it and
each value can be a point value, a yes/no or n/a. I have a lot of hidden
text boxes on the form to determine if each item selected should be included
in the overall totals or not. As I say, the form works perfectly but I
cannot for the life of me figure out how the queries need to be in the
reporting part.

You've fallen into a very common trap, especially for "questionnaire" type
database application. You're storing one question per FIELD. A better design
would be one question per RECORD, in a related Answers table.

It appears that if I store the totals from the form, my problem is solved
but I know that is a big no-no. But it does look good at the moment.

Well... it's not *illegal*, and it will run; it's just often more of a pain
than a benefit. Suppose you calculate a total on the form, and then a manager
says "Oh, Anita's performance was better than I gave her credit for" and goes
back in and changes four or five values. Can you be SURE that the total score
will adjust correspondingly?

Any ideas on how I can report on the monitor scores, when the values stored
are yes/no/na?

Not without knowing how a "yes", a "no" and a "n/a" correspond to points. You
could use an expression like

Score: Switch([answer] = "Yes", 5, [answer] = "No", -5, [answer] = "na", 0)

to generate a summable number from a text field, but I'm not sure that's what
you're getting at!

John W. Vinson [MVP]

 

[Bunky]

John,

Thank you for your time and wisdom; I've ordered a couple of books to assist
me but until they come I have 1 question.
You stated .."You're storing one question per FIELD. A better design

would be one question per RECORD, in a related Answers table.". would you elaborate on that, please? Are you saying have a table for each question?

Thanks,
Kent

First off I am fairly new (5-7 months) to Access. I had been involved in
Mainframe applications prior to that for many years.

I was asked to design a form in which would give the client the option to
'grade an agent's phone call'. (I am working in a call center environment.)
I have designed a very nice form that all management loves but . . there is
no good way to access the data once the data has been entered. I am certain
that the table design.database design is probably the problem. On the form,
I have had to make extensive use of the option groups. I might be able to
switch the form to utilize combo boxes but I still think my design is the
problem.

I'm inclined to agree.

I have a staff table and a monitor table. It is a one to many relationship.
The monitor table has all the values from the form (over 35) stored in it and
each value can be a point value, a yes/no or n/a. I have a lot of hidden
text boxes on the form to determine if each item selected should be included
in the overall totals or not. As I say, the form works perfectly but I
cannot for the life of me figure out how the queries need to be in the
reporting part.

You've fallen into a very common trap, especially for "questionnaire" type
database application. You're storing one question per FIELD. A better design
would be one question per RECORD, in a related Answers table.

It appears that if I store the totals from the form, my problem is solved
but I know that is a big no-no. But it does look good at the moment.

Well... it's not *illegal*, and it will run; it's just often more of a pain
than a benefit. Suppose you calculate a total on the form, and then a manager
says "Oh, Anita's performance was better than I gave her credit for" and goes
back in and changes four or five values. Can you be SURE that the total score
will adjust correspondingly?

Any ideas on how I can report on the monitor scores, when the values stored
are yes/no/na?

Not without knowing how a "yes", a "no" and a "n/a" correspond to points. You
could use an expression like

Score: Switch([answer] = "Yes", 5, [answer] = "No", -5, [answer] = "na", 0)

to generate a summable number from a text field, but I'm not sure that's what
you're getting at!

John W. Vinson [MVP]

 

[John W. Vinson]

John,

Thank you for your time and wisdom; I've ordered a couple of books to assist
me but until they come I have 1 question.
You stated .."You're storing one question per FIELD. A better design

No. You need three tables:

Employees
EmployeeID
LastName
FirstName
<other biographical data>

Questions
QuestionNo
QuestionText

Answers
EmployeeID <<< link to Employees, who's being rated
QuestionNo <<< what question are they being rated on
Score <<< how did they score

If Francine is being rated on 5 different measures - or 25 - you would have
one record in Answers for each question that she's subject to. You can then
very easily Sum the scores.

You might want a fourth table: ValidAnswers:

ValidAnswers
QuestionNo
Answer <text>
Score <integer>

YOur Form could then have a Combo Box displaying the valid answers for each
question, bound to the Score field in Answers; the user would see a choice
between (say) "Yes", "No", and "NA" - the Answer text - while the table would
receive 1, -1 or 0 as the score for those answers.

John W. Vinson [MVP]

 

[Bunky]

John,

Thanks a bunch; I think the light is starting to turn on but it is
flickering some. I will endeavor to make this work with the option group.

Thanks again for your insight!
Kent