DB design question

[dlburkins]

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

[dcrake]

First of all you are missing a table. This is the list of Agents, each Agent should have a unique identifier such as an auto number. This number should be stored as a foreign key in all your other tables then you can remove the Agent Name field. (Agents names may change but their primary key will not.)

Next remove all the spaces in your field names, as this will help when programming later one in the development.

Next whilst renaming remove any non alpha numeric characters such as brackets and question marks.

Once you have done that let me know.

David

[dlburkins]

OK. I went back to the drawing board . One thing that I forgot to mention before is that each agent reports to a manger. So I have to be be able to identify how much agent "x" earned under manager "y". This data is being pulled from the spreadsheet that I import every two weeks. It is also the data used from the master table. In addition I also need to have The dollar amount roll up to the Managers total.

Also the master table is somewhat different from my previous post.
I know that this is not correct, but any feedback would be appreciated.

Thanks