Callcenter Evaluation Form/Subfrom Design questions

[msr]

Hello everyone,

Boy did I get lucky this summer and landed myself a studentjob at a firm which was listed as "Converting Excel data to Access".
DAMN! I thought. This is gonna be a month of easy cruising. Little did I know what I was getting into though and even though I like to think I have some skill in Access and Basic DB design the situation is turning out to be a hard to solve one.
Instead of "converting" data into an existing database I take a look at the existing database and deduce that it isn't gonna work like this at all (I have the knowledge to deduce this atleast).
So I've been trying to design and create a database, forms, queries, etc from scratch and now I'm beginning to encounter problems beyond my knowledge and after a day of googling I can safely say my google-fu is failing me too :/ .

The Idea:



The database is a Callcenteremployee evaluation database. There's two things that it needs to do:

- Hold evaluations of employees that are based on questionlists and filled out by evaluators.

- Provide a user interface (read fancy forms) that is easy to use by people who have no idea about databases whatsoever.

How it works:

The database has an evaluation form that gets filled out by an evaluator. Relevant data in this aspect are:

- Operator Details (ID)
- Evaluator Details (ID)
- Date
- "Skill" (This is like a type, evalutions always come with a pair of "questionlists": a "Basic Skills Questionlist" and a "Secondary Skill Questionlist".

Upon filling in these details the evaluator should be presented with a form or subform that enables him to fill out the "Basic Skills" questions and the "Secondary Skill" questions.

An eg. of this would be an employee who had a call about a complaint. The evaluator listens to this call and fills out the basic info and puts the "Skill" as "Complaint".
When he has done this he should be presented with a fillout form that has the "Basic Skill" and "Complaint Skill" questions.
This could be doe via a button to generate an event or something like that. Or just an automated subform generation (if that is possible).
After filling out the questions these should be processed into the "Answer Table" (More on that in the design below). When this has happened I would like the subform to close and have focus back on the main form so you can restart evaluating from start.

The Design:

I am fairly confident that atleast the idea of my design should be good (feel free to prove me wrong though).

Because writing the design out here looked like a long job I'm linking it in the attachments (dbdesign.png).

To clarify I'll put the table translations in English here

- Opdrachtgevers = Companies that hand out the questionlists (ie. we work for them).
- Vragen = Questions
- Antwoorden = Answers
- Skills = Skills ( Skills are specific skills a callcenter employee might have. These are employer specific (eg. someone can have 2 "Complaint" skills from 2 companies)
- Evaluatie = Evaluation (This is the table I would base my "Main Form" on)
- Evaluators = Evaluators ( People who score the questionforms )
- Teams = Teams
- Team Leaders = Team Leaders
- Vestigingen = Buildings/Branches (Where teams reside)
- Operators = People who answer the phone and get graded in this database (they are part of a team and have specific skills)


How I see the Forms work:

In detail I would like something along these lines: A main form opens (Based on the evaluation table). This form gets filled in by the evaluator who then presses a button. This button will get the SkillID (from the skill field) and build a subform based on this ID.

What I mean by build is that it should query the proper questions (Basic skills + the skill that was entered in the skill field) and create a form with these questions "made into" records in the "Answers"("Antwoorden") table. It should basically already create records when it generates the form. These records should already have the relavent questionID's (Which I will have to make look like the questions, not sure how to tackle this one, rowsource/combobox?), and evaluationID filled in. So basically I would end up with a subform/questionform that is readily filllable by an evaluator.

Questions:

- Is my design good / where could I improve?
- Will this idea of subforms work / is it doable?
- Might I not be better off writing a frontend myself and using the dbfile to query data from? (I can program a bit in C#, I have no VB skills at all though)
- Any comments and feedback welcomed.

My appologies for such a long post but I figured the more info the better. Thank you for reading and hope to read some constructive responses soon.

msr

PS: I'm regularly checking this thread hourly between 09:00-17:00 from monday to friday

[June7]

Yes, sure it could be done. Save a set of answer records for the call based on the identified call ID. Each record would have call ID as foreign key as well as a question ID. Then open main form filtered to call ID, the subform is linked to main form on the call ID, this will display the records for that call ID. Subform recordsource would include join to question table so the question could be displayed next to the related answer field.