I am creating an Access database to monitor monthly performance figures for
approximately 100 call center agents. Every month, each agent will have a
total of about 15 different measurements (things like # of calls taken, % of
quality of service, # of sick days, etc.) Based on those raw scores, a
points system will assign a certain number of points to each agent for each
measurement every month. (Example: 0 sick days = 10 points, 1 sick day = 5
points, 2+ sick days = 0 points.) The points will be added up and give the
agent a total monthly score. The agent's total monthly score is normally the
only information that will be repeatedly called up once the original raw
data (the 15 measurements) have been entered.
Here is my table structure:
AGENTS (100 names)
DEPARTMENTS (4 departments)
SUPERVISORS (9 names)
YEARS (2003,2004, 2005, etc)
MONTHS (1,2,3...11,12)
DATA (15 individual #'s per agent per month)
Table notes:
- The AGENTS table is made up of 100 names, each with 1 department and 1
supervisor (both combo-boxes).
- The DATA table will contain the Year, Month and Agent name - the three
together will make up the Primary Key.
- Each agent will be listed once for each month, and that entry will
contain approximately 15 numbers (things like # of calls taken, % of quality
of service, # of sick days, etc)
I will need to have:
1) A data entry form to enter the 15 or so numbers for each agent each
month. I would imagine this will be fairly easy to set up, but your advice
is appreciated nonetheless.
2) A way to calculate the score for each agent for each month. Would I
need to have a separate table that would be populated with the 15 scores
(based on the 15 original entries), plus a total overall score? Or could my
data entry form do the calculations and add a TOTAL SCORE field to the DATA
table?
3) A way to view overall agent scores by department and by supervisor
for periods of 1, 3, 6, and 12 months. It would be ideal for them to just
dropdown the year from a combo box & somehow pick individual or groups of
months to include in the results.
In the end, I'm lost on the following:
- what parts of the database should do what portions of the job (forms
vs. queries vs. reports)
- the best place to have the calculations take place (macros?
after-update code?)
- once calculated, what data should be stored and what should be
re-calculated every time the data is viewed?
I'm sure my explanation leaves lots to be desired, and I know I'm asking a
lot. I'm very good with this type of thing, but I haven't been given enough
time to learn Access from front to back. I ask that any of you who have even
just one or two pieces of advice or guidance please put in a reply - any
little bit will help. Once pointed in the right direction, I will be able to
finish the project effectively.
Thanks,
Zed
approximately 100 call center agents. Every month, each agent will have a
total of about 15 different measurements (things like # of calls taken, % of
quality of service, # of sick days, etc.) Based on those raw scores, a
points system will assign a certain number of points to each agent for each
measurement every month. (Example: 0 sick days = 10 points, 1 sick day = 5
points, 2+ sick days = 0 points.) The points will be added up and give the
agent a total monthly score. The agent's total monthly score is normally the
only information that will be repeatedly called up once the original raw
data (the 15 measurements) have been entered.
Here is my table structure:
AGENTS (100 names)
DEPARTMENTS (4 departments)
SUPERVISORS (9 names)
YEARS (2003,2004, 2005, etc)
MONTHS (1,2,3...11,12)
DATA (15 individual #'s per agent per month)
Table notes:
- The AGENTS table is made up of 100 names, each with 1 department and 1
supervisor (both combo-boxes).
- The DATA table will contain the Year, Month and Agent name - the three
together will make up the Primary Key.
- Each agent will be listed once for each month, and that entry will
contain approximately 15 numbers (things like # of calls taken, % of quality
of service, # of sick days, etc)
I will need to have:
1) A data entry form to enter the 15 or so numbers for each agent each
month. I would imagine this will be fairly easy to set up, but your advice
is appreciated nonetheless.
2) A way to calculate the score for each agent for each month. Would I
need to have a separate table that would be populated with the 15 scores
(based on the 15 original entries), plus a total overall score? Or could my
data entry form do the calculations and add a TOTAL SCORE field to the DATA
table?
3) A way to view overall agent scores by department and by supervisor
for periods of 1, 3, 6, and 12 months. It would be ideal for them to just
dropdown the year from a combo box & somehow pick individual or groups of
months to include in the results.
In the end, I'm lost on the following:
- what parts of the database should do what portions of the job (forms
vs. queries vs. reports)
- the best place to have the calculations take place (macros?
after-update code?)
- once calculated, what data should be stored and what should be
re-calculated every time the data is viewed?
I'm sure my explanation leaves lots to be desired, and I know I'm asking a
lot. I'm very good with this type of thing, but I haven't been given enough
time to learn Access from front to back. I ask that any of you who have even
just one or two pieces of advice or guidance please put in a reply - any
little bit will help. Once pointed in the right direction, I will be able to
finish the project effectively.
Thanks,
Zed