I am creating a new db and I am trying to create a db that will accomplish a few things:
Twice a month I recieve a xls file that contains information for call center agents and their managers of their performance metrics for a sales campaign that the call center is apart of.
I currently have a massive xls spreadsheet that I use to track the process below and with the size of this intiative it will only get larger and is not scalable via an xls file. I follow the process below:
Step 1.
- Import the xls file into a master table.
- This table shows agent name, manger name, product pitched, conversation date of the sales pitch to the client and a few other columns.
Step 2.
- Calculate the payouts for each agent and section manager
Note that the agent compensation has a different payout schedule than the manager (so do i need to have a seperate table for the agents and manager?)
Step 3.
- Enter in the tax information in the xls
Each agent and manager fills out a form and signs it stating that they are going to do with their payout for the conversion period. They have three options
- Accept full amount that they have earned
- Only take a portion of the payout
- Decline to take any of the payment that they earned
In either scinario above the agent/manager still has to fill out a form indicating what they are choosing to do with their payout.
- I also need to create a section that indentifies who the agents are: FTE or Temps. As temps do not qualify for anytype of payouts but their conversion number do count towards their managers numbers.
Step 4.
- retrieve the tax documents and place then into the xls file and input the tax informaton that they signed
This process if very convoluated and if there is any suggestion anyone might have from an access perspective please share it with me.
I want to use access so that i can easly track the the number of conversions per conversion period which would be caluclated from the sum of all records for an individual for a payout period. I also want to know how much money a particual agent still has in outstanding taxes owed or how much the have forefited over a certain period of time in addition to other metrics.
Attached is a rough draft of what I mapped out
Thanks