Access 2003 - Questionnaire Type Data 3

[a98dsu98sad8sua]

Hi all,

I am trying to create a data entry form based on a call accreditation session, this involves listening to a telephone conversation and marking off certain criteria when it is fulfilled.

This is for a large enterprise (approx 600 users) so the call accreditation questions are slightly different for each staff member depending on what campaign (department) they are on!

What I need to know is, how do I present the data operator with a list of all of the questions for that particular member of staff so that they can fill out a call accreditation session? I have a table with questions, links to a table of campaigns, and a staff table with a campaign id in, and also a section id (certain criteria is under a certain section, for example, "Listens to Customer" might come under the accreditation section "Compliance").

I can't figure out how to actually construct the data entry form, and I was just wondering if someone would be kind enough to give me some help with this please in laymans terms (As I'm still pretty new, however have been told my database is in NF3 so I am quite proud!)

So the tables are:

Sessions (Where call accred. data goes)
     PK - ANSWERID
     FK - CALLID - (Linked to the PK CALLID in Calls)
     FK - QUESTIONID - (Linked to the PK QUESTIONID in Questions)
     YES/NO - RESPONSE

Questions (Where questions are stored)

     PK - QUESTIONID
     QUESTIONCRITERIA (Memo)
     FK - SECTIONID (The section the question belongs to)
     FK - CAMPAIGNID (The department the question belongs to)

Calls 

    PK - CALLID 
    CALLTIME (Time of call)
    CALLDATE (Date of call)
    FK - CUSTOMERID  (Customer the agent is calling)
    
Staff

    PK - STAFFID
    STAFFNAME
    FK - CAMPAIGNID - (Campaign Worked on)
    FK - MANAGERID - (Employee's Manager)
    FK - CONTRACT - (Employment Service - Not useful here!)

Campaigns

    PK - CAMPAIGNID
    CAMPAIGNNAME

Sorry is this is a lot to take in! Thanks very much for any help you may be able to give me so I can get this data entry form up and running!

Regards,

David

 

[hkaing79]

Can Access handle 600 concurrent users? It might be easier to use a web based front end.

I think what you're looking for is continuous forms. Lay out a template for your questionnaire, and then change the default view in property to continuous form.

 

[a98dsu98sad8sua]

Hi,

Sorry! I didnt mean 600 users! I mean 600 staff! There will be about 10 users,

 

[willir]

David

First off, you are on the right track -- separating calls / encounters / questions / answers into different tables. What you need to do is develop profiles for which group of questions to ask.

The following link is to an old post with a design schema I used to achieve such an outcome.

Table exceeding 255 fields?

The way it works is...
- Questions for the questionaire are created.
- Profiles categories are defined.
- A group of questions are assigned to a profile
Here a group of questions is created for the category -- fairly straight forward -- the next step after

The Next Step, Sharing categories...
- A super category can be created
- A group of categories are assigned to the super category.

I guess this sounds a bit complicated...
Suppose the questionaires deal with check lists for IT devices -- servers, laptops, workstations. You could create a category for each device type.

How would super categories be used? Certain questions in the above three devices would be common. Take workstations and laptops. If the task of staging end user PC's were broken into separate task groups -- preflight, stage and post stage. Both would include common tasks and other tasks would be unique for the device type. As a simplistic solution, for example, both the preflight and stage would use an identicle set of questions but the post stage questions would differ.

Using a super category would allow re-use different categories.

Richard

 

[a98dsu98sad8sua]

Sorry! I don't get it!#


I have also found out that they aren't all yes/no answers! Some of them are rated 1-4!

They also need to have 3 types of comments added to them!

Surely I can just create another table with:

CALLID & CommentNumber & Comment

So my table would look like this:

CALLID - COMMENTTYPEID - COMMENT
1 1 Comment text here
1 2 Comment text here
1 3 Comment text here
2 2 Comment text here

And then a table called commenttypes with this:

COMMENTTYPEID COMMENTTYPE
1 General Observations
2 Ways to Improve
3 CCR's Comments
4 (Future Comment Goes here)

This would allow for expandability and would also keep the database normalised.

Regards,

Toby

 

[a98dsu98sad8sua]

Here are my relationships if that helps:

 

[a98dsu98sad8sua]

Try again...

 

[hkaing79]

I don't see comments in your relationship. Is this something new you're adding on?

If it is, I can think of two approaches to take.

(1) Implement the same thing you did for question list with textboxes instead of combobox (or whatever you used for question list). Then create a main form with those two as subforms.

(2) Using one form, using your question list (I'm assuming it's a continuous form), create a form header. Put whatever extra info you want there. Then in your form footer, include all three comments and play with their visibility.

I've never used continuous form myself, but Richard is an expert. Good luck.

 

[a98dsu98sad8sua]

Oh crap!

I have just read the article "The Evils of Lookup Fields", and this is what I have been doing with my database!

With forms and stuff I have been using a combobox say for "StaffID" in Table "Calls" and using this SQL as the source: "SELECT * FROM Staff" with bound column as 1 (StaffID) and column count 2 with column widths 0;2cm.

I have just realised I have created exactly what the Lookup Wizard does!

I don't understand then! What _should_ I be doing?

Regards,

Toby

 

[a98dsu98sad8sua]

Also, do the relationships look right?

Cheers,

 

[a98dsu98sad8sua]

Hmmm... I'm not sure whether it's properly normalised or if I am doing anything right! I'm well confused! I don't understand the stuff I've read about normalisation!

 

[willir]

Okay...

To test your design, or that of others, enter some sample data to...
- prompt for the question and accept answers
- display answers in a menaingful way

If the design is easy to implement, and present, you are well underway. You can then focus on performance optimization, forms and reports.

If the design present unwanted results, then revisit the design,

If the design is awkward, then look at ways for making the queries easier.

For example, you may find that "drilling down" from the Customer -> Response requires a lot of work. A tweak would be to include the CallID on the Response.

I have also found out that they aren't all yes/no answers! Some of them are rated 1-4!

Don't let this worry you. There are several ways to handle this, and all in the Response / Answer / Session table.
- You can use type Text to accept any data and ensure the data entry is correct.
- You can define more than field on the table -- data types boolean, interger, text, date, and then display the relevant control on the form and hide the others.
- You can use an unbound control to accept the data and then move it to the correct field.

How to capture your data will depend on how you want to analyze the data. Using type Text to accept any data is fairly simple, but may present problems with the analysis.

They also need to have 3 types of comments added to them

Again, not real important. Using a Comment table is fairly good for a Help system, but does it improve a system where the end user adds comments? Again, you may have to add complexity to your SQL statements to retrieve the comments. Adding / removing comments has relatively minimal impact to the design since they are not used in creating relationships.

However, I find adding comments a good thing. It allows for qualifications, reminder notes etc. The problem with comments is that a memo field is best for this purpose, and memo fields have certain problems ... Limitations ( and advantages ) of MEMO fields

Comments also require "real estate" on the form and report.

...Responses and Sessions
Your Session table links the Call table (consequently, Customer table) to the Question table. I am not sure where the Customer / Staff enter the answer. Do they choose a Response from the Response table?

If so, how do you offer the correct responses? Based on this thread, I am assuming responses can be Yes/No and 1 to 4.
Hint:
Instead of storing the ResponseID on the Session table, just store the answer. Otherwise, you have to query to find the Response table for the answer.
If you are going to offer different responses for different questions, you have to define the relationship better. A "joiner" table will achieve this (Many-to-Many relationship), but you can achieve the samething by using a field called QuestionType on the Question table...

Questions
QuestionID - pk
Title
SectionID - fk to Sections
CapaignID - fk to Campaigns
QuestionType - text (TrueFalse, SmallInt, EnterDate, etc)

Then, you can use the QuestionType to decide on what "Responses" to offer the Customer / Staff person.

...Moving on
Sections and Campaigns
A section will have many questions. How will you decide what "sections" to ask the customer? Does a Section offer more info to database?

I assume the Capaign decides which questions to ask. How will you decide which Campaign to choose for the Customer. Is this up to the Staff person? (Meaning that a Staff person is assign to a Campaign.)

Capaigns and Managers and Staff and Contracts?
A Capaign can have many staff.
But Staff can work on only one Capaign -- is this correct?

A Manager seems to be able to manage many Staff (link not made).
But Staff can only have one Manager -- is this correct?

A Campaign will have many Managers.
A Manager can manage more than one Campaign.
We will come back to this point.

But is a Manager a type of Staff? For example a Senior Manager may manage many Managers.

A more common way of creating the management relationship is to use a type of recursion...

Staff
StaffID - pk
StaffName
CapaignID - fk to tblCapaign
ContractID - fk to tblContract
ManagerID - foreign key to tblStaff

The ManagerID points to another Staff person. The top manager(s) will manage himself, StaffID = ManagerID.

If this tweak on your design makes sense, then you will have a Many-to-Many relationship between Staff and Capaigns. (And I suspect that some Staff person can work on more than one Campaign)

...Work to be done
- Clean up Response / Session table
- Think over your manager / staff / campaign reslationship
- Create a test database and work through how well it works with queries.

Richard

 

[a98dsu98sad8sua]

Willir:

Thank you so much for your help! That's really kind of you typing so much in response!

Just to clarify:

A campaign is a service that a call centre may be running, for example - Microsoft's Tech Support Helpdesk.

We have staff assigned to a campaign, usually, staff only work one campaign but can also work on many if they would like to work extra time, I am not sure if they are assessed on this, I will have to check.

A manager _IS_ a type of staff, yes, but for the purposes of simplicity, and not wanting to create a self join, I have added them to a seperate table as managers will never work on campaigns and be assessed, and employees will never become managers (Managers in this sense are the people that response to the assesment, ie answer the questions.

The assessment is a series of questions that are sent back to the client so they can get some sort of idea how well the staff that they are paying for are doing, therefore, the client will issue the questions to us, so it's important that the questions all get assigned to a campaign.

The questions asked are decided by the campaign the employee is working on, the assessor will sit down with a recorded copy of a call (Or listen in, live) and tick off what applies to the conversation (IE, employee is polite to customer)

Senior Managers aren't in this table, only managers that conduct accreditation sessions.

Staff can only have one manager, and managers manage many staff (Many are responsible for an entire campaign, some share responsibily with another manager, I have an M:M between managers and campaigns)

Ignore the customer part, this is just a record of who the employee is calling when the assessment takes place, it doesn't influence anything in the database.

I am also not to sure how to gather all my data to be entered! I have read not to use lookups, however I used lookups a lot on my forms as obviously the foreign keys that need to be entered into my tables are numeric!

I am not too well up on queries/forms, I don't understand that much and doing a questionnaire is kind of throwing me in at the deep end!

Thankyou very much for your input.

 

[a98dsu98sad8sua]

By the way, the 3 types of comments are more like open text responses to the accreditation session, so it's stuff like "General Call Weak Points" and "Action to take from here".

I now have another table; QuestionTypes with these fields:

QuestionTypeID
QuestionType

and in table Questions I have:

QuestionID
Title (The actual criteria/question)
SectionID
CampaignID
QuestionTypeID

 

[a98dsu98sad8sua]

I've sorted the Comments side of things,

I'm having a 255 char field to store the data, and I'm putting "Comment" as a question type!

Toby

 

[a98dsu98sad8sua]

The problem is, I don't know how to sort out my data entry form! I tried running this query to bring all of the fields I need for Data Entry together:

SELECT Calls.CallTime, Calls.CallDate, Customers.CustomerName, Staff.StaffName, Questions.Title, Responses.ResponseType, Sections.SectionName, Customers.CustomerName, Customers.CustomerNumber
FROM Staff INNER JOIN (Sections INNER JOIN (Responses INNER JOIN (Questions INNER JOIN (Customers INNER JOIN (Calls INNER JOIN Sessions ON Calls.CallID = Sessions.CallID) ON Customers.CustomerID = Calls.CustomerID) ON Questions.QuestionID = Sessions.QuestionID) ON Responses.ResponseID = Sessions.ResponseID) ON Sections.SectionID = Questions.SectionID) ON Staff.StaffID = Calls.StaffID;

But it won't let me modify most of them!

 

[a98dsu98sad8sua]

I'm getting a bit further now -

I have got a form with a combo with "SELECT StaffName FROM Staff" as the row source,

And a subform with the call data in it, with this as the code for the StaffID section of the Call Table (This happens when someone changes the aformentioned combobox):

Private Sub cmbStaffName_Change()

Forms![frmDataEntry]![sbfrmCalls].Form![sbcmbStaffName].RowSource = "SELECT StaffID FROM Staff WHERE StaffName='" & cmbStaffName & "';"

This gets the correct StaffID when I change the dropdown, now all I need to do it set that as the actual field value but I don't know how to!

 

[a98dsu98sad8sua]

I have also found a query I can use to enter data:

SELECT Calls.*, Staff.*, Sessions.*, QuestionTypes.*, Questions.*, Responses.*, Sections.*, Customers.*
FROM Customers INNER JOIN (Sections INNER JOIN (Responses INNER JOIN (QuestionTypes INNER JOIN (Questions INNER JOIN ((Staff INNER JOIN Calls ON Staff.StaffID = Calls.StaffID) INNER JOIN Sessions ON Calls.CallID = Sessions.CallID) ON Questions.QuestionID = Sessions.QuestionID) ON QuestionTypes.QuestionTypeID = Questions.QuestionTypeID) ON Responses.ResponseID = Sessions.ResponseID) ON Sections.SectionID = Questions.SectionID) ON Customers.CustomerID = Calls.CustomerID;

It's basically every field from every table but it allows me to enter data!

 

[a98dsu98sad8sua]

I don't think I know enough about SQL/VBA to do this, I'm going to have to figure something else out. It's a bit of a steep first project and I'm losing my patience with it now.

 

[willir]

Sorry thOrOn

I had other committments today...

We have staff assigned to a campaign, usually, staff only work one campaign but can also work on many if they would like to work extra time...

This is still a many-to-many relationship so that you can offer the correct set of questions to the end user / staff person.

purposes of simplicity, and not wanting to create a self join, I have added them to a seperate table as managers

Understood. Not too important -- may have duplicate entries -- one in management and one in staff tables. Note: Do not delete Staff records since they are tied to Calls.

it's important that the questions all get assigned to a campaign.

You have achieved this with your design.

Staff can only have one manager

With the accepted rare event when a staff may become a manager, your design is fine.

Ignore the customer part, this is just a record of who the employee is calling when the assessment takes place

Thanks for the clarification.

I am also not to sure how to gather all my data to be entered! I have read not to use lookups, however I used lookups a lot on my forms as obviously the foreign keys that need to be entered into my tables are numeric!

Lookups in the Table Design or Lookups in aggregate functions? DLookup, etc. This is an approach that can be tweak later. DLookup works and is easy to use. But there approaches that are faster which can be added later.

I am not too well up on queries/forms, I don't understand that much and doing a questionnaire is kind of throwing me in at the deep end!

Agreed -- very deep end. A questionaire database is not the easiest thing to do.



Richard