Proper ERD

[Zealotwraith]

Good day,

I am coming from an Excel background so I run into a few clashes, so forgive ahead of time,

I am trying to learn access to better help with DB,

The scenario I am working with currently is a call center bonus structure (one to help a friend and two to learn / test access)

I have the excel version of it working for him just fine and now thought i could learn access on my own with it ...

One of the hardest parts to start out with a DB is planing it out, so an ERD is needed,

Below are the tables I have constructed so far but wanted to know how to link them since when I did, I ran into some issues,

HRDB is the human resource Data base with all the bells and whistles of a employee,

The Criteria table which is the center for comparison shows:
each department (there are 5)
the Role of each employee (let say 3 for starters)
The Category (5 categories which each employee is going to be graded on)
The grade for each of the categories (some have A or B, others have A,B,C,D so there is min 2 attributes max 4, [some have 3])
Criteria being (>=, >, = , <, <=)
Requirement (A numerical value that will be compared to the score the agent recieves with the criteria)


ReqTime (required time needed) [Created a separte column because time is a difrent data type]
Qulifier (Binary) yes or no
Payout (how much the employee is paid depending on their score,

Then the raw data which is the average score for each of the Categories compsed of the agents name and their score for that criteria,
(I believe I should add an ID as an auto number)

The query I want to build will show the employees ID, name, Dep, Sub-Dep, (the score they obtained for the category, how much they get paid) x for each category, Sum of pay out,




I'm willing to start from zero if needed, as well as adding new tables but I am not sure where to go,

I dint draw any relationship lines yet because I think i need interval tables before I get there ....

In my Access DB I was using the name of the reps as an PK for each of the categories and I think that was one of the mistakes I had,

I could just treat access like an excel sheet and hard code the criteria as below:

QA_Payout: IIf([QA_Score]>90,100,IIf([QA_Score]>80,50,10))

but I wanted to see if there was a better way to properly use access,

[recyan]

Till some one comes along, just a few thoughts :
For eg. pertaining to Table HRDB

1) An Employee joins the company. Leaves the company. Rejoins the Company.
2) An Employee, during his period of Employment, can belong to different Departments / Sub-Departments during different Periods..
3) Do not know, what Sector / Seccion mean.
4) An Employee, during his period of Employment, can have different Supervisors during different Periods.
5) An Employee, during his period of Employment, can have different Roles during different Periods.

Thanks