Creating a Monitoring/Quality Assurance database

[danielrogers1]

I'm moderately new to Access, and this is certainly the largest/most difficult task I've undertaken in it.

I am needing to set up a database to account for approximately 70 temporary employees who perform telephone surveys. We do random live monitors of their phone calls, and need to rate them on 24 categories as having received full, partial, or no points in that category, or the category being not applicable to their call.

I currently have a form for Interviewers, a form called Agencies to keep track of which Agency each Interviewer works for, and said agencies salary/contact info, a form called Questions where the 24 categories are listed, and a form called Monitors which I have created a form for.

What I'm needing info on, or what I think I need info on at this point, I may need to redesign the whole thing based upon your guys' input, is how to link the possible points available for each category into a field on the Monitors table, so that the total possible points can be used to create a score % for the monitor in question.

I've attached the database I've got so far. I haven't put any information into it yet, I'm just trying to get the layout to work, and I'm really sort of at a loss on how to proceed. Any help will be GREATLY appreciated.

Database4.zip

[June7]

Multiple similar fields indicates a non-normalized structure, as in the Monitors table. If you wanted to add another question, would mean modifying table, queries, forms, reports to accommodate. It also means linking questions with answers is nearly impossible. And summing the points can't be done with an aggregate query, would mean a very long expression adding 24 fields.

Consider:

tblMonitorEvent
IdNum (primary key)
EmpID (foreign key)
InterviewerID (foreign key)
MonitorDate

tblMonitorPoints
EventID (foreign key)
QuestionID
Points

[danielrogers1]

Thanks so much. Where would I get the EventID in tblMonitorPoints? Would that be the IdNum from tblMonitorEvent?

Would I then just create a form from tblMonitorEvent and subform the Questions table?

[June7]

Yes, those are the primary and foreign key linking fields.

Main form bound to tblMonitorEvent and subform bound to tblMonitorPoints with a combobox to select the QuestionID.

[orange]

Daniel,

You could learn a lot by working through this tutorial. It will show you how to design tables and identify relationships.

Good luck

[danielrogers1]

Thanks for the link on the tutorial. I'm familiar with most of the stuff for setting up tables and relationships. I'm really struggling with how to get it to show up into a form to make it easy for my employees who are doing the actual monitoring to understand what they're looking at and put in the information without messing up the record structure.

For instance I tried to subform the Questions table, but it only shows the first record (first question) rather than showing all 24 of them, so that they could input values for the points earned on each category.

Is there a tutorial that shows how to create and format forms, etc that would cover these issues?

[June7]

The Questions table is source for standardized questions, not where you want to store the evaluation points. That's tblMonitorPoints. User would create record for each question as they performed data entry.

If you want to present the user with all the questions requiring response, then need to create the records in tblMonitorPoints and open form to this set of established records and they just complete the data entry.

Batch creation of records can be done with an INSERT SELECT sql action.

[orange]

There are lots of youtube videos, but these vary in quality and info.
Are you looking for Form/Subform, or are you looking more for How to design a Form for User Access?

Do some research google/bing.

Post back if you have more info/details.

PS: Most people with post count of 3 are usually stuck on some technical detail of Access -- because of their table structures.
You seem to be beyond that - good .

[danielrogers1]

Database4.zip

Really I just need basic functionality at this point. I can worry about setting up login screens and user access forms later. This is what I've come up with so far based upon June7's suggestions, I made a query subform of the questions to display the questions, and now I need to figure out a way to get points for each question entered into the monitor points, and create a simple query that will allow us to search for all of a given interviewers monitors within a given timeframe and their average scores.

I know nothing at all about sql. I'm familiar with program in SAS but that's really the extent of my programming ability.

Again, any help at all is greatly appreciated. I've watched a bunch of youtube videos and been googling for weeks, and while I've learned enough to be able to get this far, there really doesn't seem to be much specific to what I'm needing to create.

[June7]

The form/subform should be as I suggested in post 4.

[danielrogers1]

If I set it with a subform of tblMonitorPoints with EventID as primary key linked to tblMonitorEvent, won't that only allow me to enter points for 1 question per monitor, since the eventid has to be a unique number I can't have multiple questions for each event id?

Or am I misunderstanding something? I'm sorry if I'm being dense, I just want to understand the logic so in the future I won't have to ask once I get the concept.

[June7]

IDNum is primary key in tblMonitorEvents. EventID in tblMonitorPoints is foreign key field that will receive the IDNum value.

Each event will have many associated question/answer records and each question will be associated with many events. This is a many-to-many relationship. tblMonitorPoints will have many records associated with each record in tblMonitorEvents. Review http://office.microsoft.com/en-us/ac...010098674.aspx