Case Base Help

[Thepieman]

I am new at this so can you help please ?

I am currently building a database for use by a small IT Team which will allow a receptionist to record details of IT Faults which will then be adopted by an engineer. My problem is that I want to add some sort of system that will prompt questions the receptioist to ask the right qustions dependent on the type of problem reported e.g. email, hardware, software etc.

Any suggestions on the best way to do this ?

 

[reclusivemonkey]

Create a table containing the different categories you want. (i.e. email, software, hardware, etc.). Use an AutoID to give each one a number. THen create a table with all the questions you want to ask, and add a field to give them an 'ID' category for what area the questions relate to. It should be fairly easy then to link these up so depending on the category, the right questions come up.

 

[The_Doc_Man]

If you are comfortable with VBA, this is not really hard, though it might be tedious, but ... consider this

tblTopic
fldTopicID, PK, autonumber
fldTopicName, text - and this field drives the drop-down for picking the type of problem....
fldFirstQuesID, FK, points to the first question to ask for this topic.

tblQuestions
fldQuesID, PK, autonumber
fldQuesText, text - the question that gets asked...

tblTopQuest
fldTopicID, FK, long
fldQuesID, FK, long
fldQuesType, coded: perhaps 1 = yes/no, 2 = pick answer from short list, 3 = a number of some sort, 4 = answer is memo field

Why, you ask, is fldQuesType not part of the tblQuestions table? Because you might have different answers condoned for the same question under different topics... so the question type could be YES/NO for a network router question but YES/NO/CANNOT TELL/NOT PRIVILEGED TO DO THIS for a similar action on a different kind of intelligent box.

tblAnswers
fldAnswerID, PK
fldAnswerType, code: 1 = answer from list, 2 = requires MEMO, 3 = requires number, 4 = YES/NO answer, etc. etc.
fldAnswerText, text

This table includes simple answers like Yes, No, Blue Screen of Death, Blank Screen, Smoke rising from object, User's Fist Embedded In Screen, etc. etc.

tblCondonedAnswers
fldQuesID, FK
fldAnswerID, FK
fldCAValue, the code stored when the given answer is picked...
fldNxtQues, FK, if NIL then no more questions can be asked.

Now, what you do is use Condoned answers to populate a drop-down given that you know the question ID, and you can look up the text of the condoned answers with a Lookup or JOIN as you please. And with the NxtQues field, you know the next question to ask for each condoned answer.

Note again that the next question to ask is derived from the table where both the question and the answer are keys. A YES answer for one question might be the end of the sequence, but a YES answer for another question might lead into a hellacious raft of further questions.

OK - that is the QUESTION & ANSWER side of the house.

Now, what do you store?

Well, you need something like this:

tblProblem
fldProbID
fldCallStart, Date/Time
fldCallEnd, Date/Time
fldProbResolution, code (see later)
fldUserInfo (and I'm glossing this because you can design whatever user tracking you desire here. You will probably want to consider a separate table with a Validated User ID autonumber, and just store the ID number rather than the actual customer name. Adding a new validated user is a whole other can of worms in itself...)

When a user calls, your answering-desk person get a new problem ID. The first thing the answer person sees is the Validated User Drop-Down, followed by a Topic Drop-Down. Now, here is where your VBA stuff gets involved...

You display the first question based on the fldFirstQuesID in the topic table. You look up the list of condoned answers to the question and display the drop-down list containing the condoned answers. You get an answer. Each answer entry for a question has an implied follow-up question unless the answer is a memo answer or you have exhausted the possibilities.

For each answer, you might store...

tblProbQA
fldQuesID, FK - the question that was asked
fldAnswID, FK - the answer that was chosen (and one of the possible answer codes is MEMO)
fldAnswOrdinal, long - the order in which the question was answered (1st, 5th, 237th, etc.)

Now you ask questions and get answers to basically traverse a question tree. The traversal ends when you reach an ANSWER that has no next question. When the answer is a MEMO field, you have a table like this:

tblProbQAMemo
fldProbID, FK
fldQuesID, FK
fldAnswText, MEMO

When you are finished, you link all answers based on the problem ID, then make detail records based on the question ID and either the answer text from the lookup or the memo field as appropriate.

You should examine this and consider whether you had something else in mind. I tend to make complex answers when I think the problem is complex, which in this case I do. In ANY case, treat this as another viewpoint, a starting point for exploration and analysis, and a straw-man for discussion.

Issues NOT described above: How to handle follow-up calls by the same user on the same problem; how your second-response technician records supplemental info learned by calling the user to ask "What the hell did you mean when you said 'the retractable cup holder is broken?' ..."

You also need to have some problem disposition codes that are not necessarily revealed to your user base. Codes like, Sent Part, Sent Tech to Site, Talked Customer Through Workaround, PICNIC (Problem In Chair, Not In Computer), etc. etc.

Finally, you need to consider things like archiving because trust me, problem ticket databases tend to bloat like microwaved frogs...