How to make sure each staff have their own details in same category? 1

[awinnn]

Hi,
I have form that displays staff info and subform that displays competency records. My problem is with the subform. This is the sample of data,

StaffNo : 12345
StaffName : John
Category : Exec

Competency Point1 Point2
Communication
Teamwork
Ability

The form should be like above. I need to key in Point1 and Point2 only. The Competency records depends on the Category. Each Category has different Competency records. My problem is when i enter Point1 and Point2 for this staff, all the staff within the same Category has the same points. How should i do so that every staff have their own points? Any idea?

Thanx in advance..

 

[JoanneM]

Hello

Do you have fields for
communication_Point1
Communicationn_point2

or do you have

communication
teamwork
ability
filtered on category?

because if you have the second and you are allocating unbound txtboxes for the point1 & point2 you will see what you are presently seeing


To achieve the desired result you need to create points fields for each of the competency records

so you would have
staffid
Communication_Point1
Communicationn_point2
Teamwork_Point1
Teamwork_point2
Ability_Point1
Ability_point2

You would then be allocating points to the the staff member not the category.

I hope this helps a little

regards

Jo

 

[awinnn]

Hi Jo,
Thanx for your reply..
This is what i've done..

I have 3 tables, StaffDetail, Category and Competency. StaffDetail has (StaffNo, Name, CategoryId). Category has (CategoryId, Desc). Competency has (CompetencyCode, Point1, Point2, StaffNo, CategoryId).
Category has 1-to-many relationship to StaffDetail and Competency. And StaffDetail has 1-to-many relationship to Competency.

Yes,
communication
teamwork
ability
are filtered on Category. Each Category have their own Competency.

communication
teamwork
ability
is a sample of data for Category Exec, not the field. Is it possible to create their own points?

Any idea?
Thanx again..

 

[JoanneM]

Hello again

Pardon the delay but could you forward db to email2jo@btinternet.com

cos I think I may have to cobble up some code for you.

I may be thinking too deeply and I'd just like to check how your subform is linked.

jo

 

[johnnymac43]

Your data structure does not cater for the many to many relationship between StaffDetail and Competency. To achieve this, split your Competency data into two tables. The new table (CompetencyDetail) will have a many to one relationship to StaffDetail and a many to one relationship to Competency. It will contain the Points data for each staff member for each competency that he/she has (or should have). The Competency table will contain only one record for each competency for each Category.

HTH

John

 

[awinnn]

Hi,
Jo,
Thank you very much for your help..
1 star for you..

John,
I already solved the problem..thanx anyway..