Hi, I am new here and somewhat "access challenged". I have built some simple DB but it has been 5 years or so and now have been given this project.I work in a call center and this is going to be a incentive report for the agents.

The goal:
Find out if the agent has meet the following criteria in order to get the "payout".
* Minimum # 150 calls a day
* Talk time of under 63 seconds
* after work time of under 3 seconds
* No missing days at work
* no 5 min + late to works
* less than 4 1-4 min lates to work.

I am working with the call center manager and it seems all of these things will be "import" from various excel documents ( created the forms so that occurence and attendance can be maintained in Access instead of importing). I made the following:

AgentTBL: PK Agent Id, Name
AttendanceTBL: Name, Date, Desc, Value (the value can range from .5-1.0 depending on how long)
OccurenceTBL: Name, Verbal or Written, Date, Desc
PerformanceTBL: Name, Calls, TalkTIme, AfterWorkTime, AvgSpeedAns

OccurenceFRM, input form (with all fields) for the occurence table
AttendanceFRM, input form ( (with all fields) for the attendace table

What I am unclear about at this point is how to RELATE the tables....Should I have a PK in all of them? SHould it be the same field like AgentID? All of this needs to make it to a Query to see if they need the requirements and then a report for the call center manager. Any input would be APPRECIATED to the fullest. If there is anything else you need to know, please ask, if you want me to upload the file, let me know that too.

Respectfully,
Monique

Hi Monique



Can you upload a zipped copy of your Db??

Alright, I know it is a sad little DB

Any help is greatly appreciated.

Hi Monique

Had a quick look and have the following observations

Table 1 - Agent

this should be renamed to tblAgent

EmployeeID which you have set as a Primary Key - this should be set as an Autonumber and NOT Number

You also need to decide on what you are calling the people in this table they should either be Employees or Agents and NOT
a mixture of BOTH

You should NOT have any spaces in fieldnames ie First Name should be Firstname etc

Department = instead of allowing the user to type the Department Name which always leads to Data Input errors, this
field should be named DepartmentID - set as a Number DataType (This would then be linked to a separate table named tbluDepartmentList)

Tables - Performance - Attendance & Discipline I take it are to be used to store data about EACH of the Agents/Employees listed in tblAgent?

Your Discipline Action table contains a number of Yes/No fields. These shoudl be records in a separate table rather than fields in the Discipline table

I am working with the call center manager and it seems all of these things will be "import" from various excel documents ( created the forms so that occurence and attendance can be maintained in Access instead of importing). I made the following:

AgentTBL: PK Agent Id, Name
AttendanceTBL: Name, Date, Desc, Value (the value can range from .5-1.0 depending on how long)
OccurenceTBL: Name, Verbal or Written, Date, Desc
PerformanceTBL: Name, Calls, TalkTIme, AfterWorkTime, AvgSpeedAns

OccurenceFRM, input form (with all fields) for the occurence table
AttendanceFRM, input form ( (with all fields) for the attendace table

What I am unclear about at this point is how to RELATE the tables....Should I have a PK in all of them? SHould it be the same field like AgentID? All of this needs to make it to a Query to see if they need the requirements and then a report for the call center manager. Any input would be APPRECIATED to the fullest. If there is anything else you need to know, please ask, if you want me to upload the file, let me know that too.

Monique,

Here's a quick diagram of what's going on...

EACH (1) Agent CAN HAVE zero or more Attendance records. Each Attendance record CAN HAVE only one related Agent record.
(same is true of Agent/Occurrence and Agent/Performance). This step is crucial because you can determine which relationships are many-to-many, which you need to break down into two one-to-many relationships. The easiest example is Products and Invoices. One Invoice can contain many Products, but the reverse is also true: One Product can appear on many Invoices. (so the relationship is many-to-many, which a database can't handle.) So you break the m-m relationship into a junction table (in this example, InvoiceLineItems, which consists of ProductID, InvoiceNumber, Quantity, and any other columns that are related to individual Line Items...)

Agent---(1,M)---Attendance.
Agent---(1,M)---Occurrence (of a write-up?)
Agent---(1,M)---CallStats/Performance

AgentID is PRIMARY KEY of Agent table (PK uniquely identifies a record).
AgentID is FOREIGN KEY of Attendance, Occurrence, and CallStats/Performance because the foreign key joins the attendance (etc) records back to their owners (Agent records in the Agents table).

Thanks Mike, those changes are made...what do I do with relationships to get all that data to eventually come together?

Thanks Mike, those changes are made...what do I do with relationships to get all that data to eventually come together?

Hi Monique

As mentioned by MadPiet you need to add Foreign Keys to all related tables so that your table structure would be like this:-

tblAgent
-AgentID - PK - Autonumber
-Firstname
-Lastname
-DepartmentID - Number - FK (linked to tbluDepartment on PK DepartmentID)
-Phone
-AltPhone
-AlarmCode
-2DigitCode

tbluDepartment
-DepartmentID - PK - Autonumber
-Department

tblPerformance
-PerformanceID - PK - Autonumber
-AgentID - Number - FK (Linked to tblAgent on pk AgentID)
-Calls
-Att
-ACW
-ASA

tblDisciplinary
-DisciplinaryID - PK - Autonumber
-AgentID - Number - FK (Linked to tblAgent on pk AgentID)
-WarningTypeID - Number - FK (linked to tbluWarningTypes on PK WarningTypeID)
-IncidentDate
-Description

tbluWarningTypes
-WarningTypeID - PK - Autonumber
-WarningType

tblAttendance
-AttendanceID - PK - Autonumber
-AgentID - Number - FK (Linked to tblAgent on pk AgentID)
-DateofOccurance
-Description
-Value

You guys are awesome!

How does this look so far?

Hi Monique

Not bad for first attempt

What you now have to do is Double Click on Each of the Joins and apply Referential Integrity and Cascade Updates
to each of the Joins between tblAgent and the 3 related tables

For the Joins between the Lookup Tables do the same ie Enforce Referencial Integrity and Cascade Updates. Also
set the Join Type to Option 3

The Relationship diagram should then look like this:-

Click to view attachment

Hi Monique

Why have you got FirstName, Lastname in the 2 related tables??

Its deleted now so the first and last name are only on the agent table.... The relationships are like yours now....so I can start the forms and querys now?

Hi Monique

Yes you can now start to create Forms for Data Input