Table creation for comparison

[Zealotwraith]

Good day,



I’m new to this forum so bare with me if my format is a bit off (if it is please let me know so I can correct it for the future)

I have been dealing with a few "simple" data bases mostly in excel, got to manage creating reports and macros quite well and I keep getting told that access is better for handling information because it can handle more information and is a bit more powerful with queries and what not ...

I completed my undergraduate studies in Computer science mostly dealing with java and C++ and touched some basics about DB but still have questions,

Looking into a few you tube videos for tutorials and help I still have a few questions,

Since I am use to excels format, I'm not too sure on how this is best applies to Access,

I want to create a table for comparison purposes,

my problem comes to I'm not sure if I should just create one large table or several small tables? and where is the happy median of this?

I want to use this values to be able to use a query or report to compare to the scores obtained by individuals depending on their group and team to see what their final score would be,





In excel I just have have it set up as a formula,

=IF(G5="-","DNQ",IF(G5>=88,50,IF(G5>=83,20,IF(G5>=80,"-",IF(G5<80,"DNQ")))))

but i would like to try and learn to use it as a table,

I kind of put everything in one table and before adding the other teams and groups, wanted to check if this was right?

should I create a different table for each team? what about each group? is it necessary for me to normalize the table?

if each criteria is different depending on the team and group,

thanks for your time, help and advice,

[June7]

That table is probably fine as is, although don't know enough about relationship of teams and groups. Can each team be in more than one group?

You could have additional tables to hold information about the teams and groups, such as:

tblTeams
TeamID
TeamName
TeamCaptain

tblGroups
GroupID
GroupName
GroupLeader

And if you want to have info about team members:
tblMembers
MemberID
MemberLast
MemberFirst

And if you want to associate members with teams:
tblMemberTeam
MemberID
TeamID

[Zealotwraith]

Thanks for the suggestion,

I have an Human resources DB for specifics information as to who is in what team and what teams make each group,



my biggest question really is if i should leave the Criteria table as 1 big table for each group and each team?

or should each group have its own mini table?

This is to say, Should I create Table 1 for category U, Table 2 for Category A1, Table 3 for Category A2 etc ...

or should I put them all in 1 table? does it make a difference to the performance?

for criteria my PK is the Team / Group / Category / Grade ... since each group and team has different requirements for each category ....

i have notices most tables Ive seen on you tube are "small" and they use query's to create a "larger" groups of info....


any suggestion as to where to learn how to set up my table before creating it .. as in to map how it will look?

[June7]

Do not create separate tables for teams and groups. What you have is appropriate. Your criteria table is not large, i.e. not a lot of fields. The number of records is irrelevant.

Research database normalization and entity relationship diagram.

[Zealotwraith]

Thanks a lot!, I will try and read on those ^^

I got to the query part now ....

This is what it looks like in excel and is my end goal ....

=IF(G5="-","DNQ",IF(G5>=88,50,IF(G5>=83,20,IF(G5>=80,"-",IF(G5<80,"DNQ")))))
Now I am able to hard code the function in as below and it works fine


For Acces :
QA_PayOut: IIf([QA_Score]>88,50,0) (Works Fine)

QA_PayOut: IIf([QA_Score]>90,100,IIf([QA_Score]>80,50,10)) (Works fine)


Would I be able to Replace each value for a variable from another table?


QA_PayOut: IIf([QA_Score][Criteria][Requirement],[PayOut]) (Theoretical not working)

or how would i be able to use an if statement using variables to go through the table, compare a "set" value called QA_Score in this case, to each of the requirements so that I can calculate the payout?

of the only way is to hard code it?

Would this work with my previous table which i called "large" ? or would I have to create a separate table for each?

Category	Grade	Criteria	Requirement		Qualifier	Pay_Out
QA	A	>=	90		Yes	$100.00
QA	B	>=	87		Yes	$50.00
QA	C	>=	85		Yes	$0.00
QA	D	<	85		No	$0.00

[June7]

One way is for each record in the criteria table to have a unique ID (autonumber type field can accomplish that). Then save ID of appropriate criteria record into other table record. Build a query that joins the table on the primary and foreign key ID fields. This will make all related fields available for use in expression. This is the whole purpose of relational database.

Hint: check out the Switch and Choose functions, often neater then nested IIf.

[Zealotwraith]

You are the best! thanks!

but can you give me an example of what it would look like?

I read about both functions and see that it will allow to pic an element in the array, however I'm not sure how to implement them to do properly function

Right now my criteria looks like so ... would it be better to make a table for each "Category"?

right now this is for 1 department ... I am missing 4 more that look the same but each requirement / criteria may defer from department to department,

ID	Department	Team	Category	Grade	Criteria	Requirement	Requirement_Time	Qualifier	Pay_Out
1	HE	Specialist	Utilization	A	>=	80		Yes	$0.00
2	HE	Specialist	Utilization	B	<	80		No	$0.00
3	HE	Specialist	Atendance	A	<=	3		Yes	$100.00
4	HE	Specialist	Atendance	B	<=	5		Yes	$50.00
5	HE	Specialist	Atendance	C	<=	7		Yes	$25.00
6	HE	Specialist	Atendance	D	<=	9		Yes	$0.00
7	HE	Specialist	Atendance	E	>	10		No	$0.00
8	HE	Specialist	QA	A	>=	90		Yes	$100.00
9	HE	Specialist	QA	B	>=	87		Yes	$50.00
10	HE	Specialist	QA	C	>=	85		Yes	$0.00
11	HE	Specialist	QA	D	<	85		No	$0.00
12	HE	Specialist	NRV_Count	A	<	3		Yes	$50.00
13	HE	Specialist	NRV_Count	B	<	7		Yes	$25.00
14	HE	Specialist	NRV_Count	C	>=	7		No	$0.00
15	HE	Specialist	NRV_Time	A	<=		0:15	Yes	$0.00
16	HE	Specialist	NRV_Time	B	<=		0:30	Yes	$0.00
17	HE	Specialist	NRV_Time	C	>		0:30	No	$0.00
18	HE	Specialist	Productivity	A	>=	35		Yes	$50.00
19	HE	Specialist	Productivity	B	>=	33		Yes	$20.00
20	HE	Specialist	Productivity	C	>=	30		Yes	$0.00
21	HE	Specialist	Productivity	D	<	30		No	$0.00
22	HE	Representative	Utilizatiom	A	>=	80		Yes	$0.00
23	HE	Representative	Utilizatiom	B	<	80		No	$0.00
24	HE	Representative	Attendance	A	<	10		Yes	$0.00
25	HE	Representative	Attendance	B	>=	10		No	$0.00
26	HE	Representative	QA	A	>=	88		Yes	$50.00
27	HE	Representative	QA	B	>=	83		Yes	$20.00
28	HE	Representative	QA	C	>=	80		Yes	$0.00
29	HE	Representative	QA	D	<	80		No	$0.00
30	HE	Representative	NRV_Count	A	<	3		Yes	$50.00
31	HE	Representative	NRV_Count	B	<	7		Yes	$20.00
32	HE	Representative	NRV_Count	C	>=	7		No	$0.00
33	HE	Representative	NRV_Time	A	<=		0:15	Yes	$0.00
34	HE	Representative	NRV_Time	B	<=		0:30	Yes	$0.00
35	HE	Representative	NRV_Time	C	>=		0:30	No	$0.00
36	HE	Representative	Productivity	A	>=	37		Yes	$50.00
37	HE	Representative	Productivity	B	>=	33		Yes	$20.00
38	HE	Representative	Productivity	C	>=	30		Yes	$0.00
39	HE	Representative	Productivity	D	<	30		No	$0.00
40	HE	Representative	All	A	=	1		Yes	$50.00
41	HE	Representative	All	B	=	2		Yes	$30.00

[June7]

That query does not show the [QA_Score] field. Can't do calc without it. Have you saved the ID as foreign key in the other table? Build query that joins tables.

Do not see need for table for each Category (Note: misspelled Utilizatiom).

Will need Eval function:

IIf(Eval([QA_Score]&[Criteria]&[Requirement]),[PayOut],othervalue)

[Zealotwraith]

thanks a lot June!

appreciate the help ^^ and thanks for bearing with me,

I think i found my biggest problem which is coming from a spread sheet orientation from excel to a relational DB, (not to mention I'm diving into a big project all at once)

I started to go over the basics, and thought about making a few mini projects before tackling the big one,

One of the difficulties I find myself having is how much normalization is needed? (like how do i know if I should create a table for each group or leave it as is?)

one thing i think may be useful would be, when ever in excel I use the Vlookup function, indicates i need a new table for that value I'm looking ....



Regarding the conceptual part of Normalization, another issue I have is, normally the PK is used as a number or ID, that number is referenced to the persons name,

in another table, that persons ID is listed to another characteristics such as the example below,

How do I manage (in my mind) when creating the 2nd table that ID X corresponds to peron Y so that I can put characteristic Z?

this is to mean .. I can remember ted is ID 1 when making the 2nd table for 3 or 5 employees,

but what about when I deal with 300 or 500 ppl ... I'm not going to be able to remember what person corresponds to what ID,





I jsut got the Access for Dummies Book and have been reading up on Relational DB and a few more YouTube videos,

any recommendations on what else would be "helpful" to understand on a conceptual level before diving in?

although I agree diving in and making mistakes / running into problems is the best way to learn ^^

[Zealotwraith]

Please bear with me as experimentation is half the fun, also since I am in a learning process I may take some things further than they are needed just to experiment with them ^^,

I do make some references to excel since its what i know and understand, not that I am making a comparison but more of trying to logically link the two so I can transition to access,

The function work, however there is something wrong in the execution of it,

"IIf(Eval([QA_Score]&[Criteria]&[Requirement]),[PayOut],othervalue)"

the only thing is that it evaluates the QA score for each of the category,

This means for each "agent" it runs all 41 different comparisons to the same QA Score, (most of which are not needed since they should compare to a different score)

when running the query it will run each possible outcome, rather than a nested if, which will only yield one possible outcome per person (which is what i wanted)

So i believe a nested if would be better (unless if its that i did something wrong or you see a better way of doing this)...

the only other thing that i am curious about is if there is a way to soft code the nested if,



maybe I am making some assumptions but let me see if I can lay most of it out, (some of this is background info to see how far I can take access plus me thinking out loud)

the QA score is a numerical value that ranges from 0 - 100

for every agent they receive 4 or more QA scores that are averaged out, (the QA table is just the agents name and the average score)

I need the query to compare that average score to see how much they should get paid for it,

If the agent was able to get 90 or above, they should receive $100 ,

if the score ranges between 89 and 80, they should receive $50,

if the score is below 80, they are disqualified (DNQ or did not qualify),

QA_Points: IIf([QA_Score]>90,100,IIf([QA_Score]>80,50,"DNQ"))

However another problem I can foresee is that QA points is a numerical value, while the expression DNQ is a string,

Since Excel works as individual cells I don't have to declare data types, any suggestions to work around this?



lastly, in excel I am able to group rows of related information,

is there a way to do this with tables? as in selecting 4 tables and have them grouped under one name or section?

or by definition that is what a query is? a grouping of information from different tables?

[June7]

but what about when I deal with 300 or 500 ppl ... I'm not going to be able to remember what person corresponds to what ID

A multi-column combobox will probably help you with this. The combobox can be set up to display names but the ID value will be saved.

Your criteria table looks normalized to me.

I don't really understand the issue you describe with the expression. The expression I suggest just determines if there should be a payout. Displaying a letter grade in place of the score value is a different issue and that would involve the nested IIf Or Switch function.

for every agent they receive 4 or more QA scores that are averaged out

Is this 4 records or 4 fields that need to be averaged? The first would be done in an aggregate GROUP BY (Totals) query. The latter requires a calculated field and possibly a custom VBA function.

I suggest you get a tutorial book on Access and get an understanding of the basics before tackling this project.

[Zealotwraith]

Thanks, Ill give it a go ^^,

lastly, any recommended topics where to start?

I went as far back as ERD to "plan" out the structure before getting into the handling of it ...