Having problems with nested iif

[LSHULSTER]

Hi All,

I have created a database that records whether our staff have specific skills relating to the use of software program. Some skills are mandatory for some staff to have and some are optional. We plan to assess annually whether they possess each skill. I want to be able to report on how many staff have all their mandatory skills at each assessment but I run out of space in the expression builder in the query that i was building and I don't have much experience with vba. The code i was using in the expression builder of my query looks something like this.

Proficient: (IIf([Skills]="CHANGE PASSWORD" And [Skills]='OUT OF OFFICE' And [Skills]='CHANGE PERSONAL INFORMATION'........,"Proficient","Not")).

In this example I stopped at three skills but there could be up to 30 that would be necessary for a person to be deemed proficient at their assessment and that is why i am running out of space in the expression builder window.

Are there other easier ways to do this. Thanks so much in advance for your help

[jzwp11]

Welcome to the forum!

Could you provide some information on how you have your tables structured (table names, field names and relationships)?

[LSHULSTER]

Sure thanks for the reply.

I have three main tables
tblStaff (this describes the staff, staffid (primary key), name, phone number, program, role, etc)
tblAssessment (this has an the foreign key for staffid and primary key assessmentid it has details of the assessement like the date of the assessment) this is linked one to many to the tblStaff.
tblSkills (this is where the skills for each assessment are stored. It contains the skillsdesc, skillsid (primary key), assessmentid (foreign key). It is linked one to many to the tblAssessment.

Does this help

I also have two other lookup tables that organize the skills into categories but they are only linked to each other an not to the other tables.

[jzwp11]

If a skill can apply to many assessments and an assessment can have many skills that is a many-to-many relationship. Basically, that says that the skills should be in their own table

tblStaff (this describes the staff)
-staffid (primary key),
-name (note the word name is a reserved word in Access and should not be used as a table or field name; see this link for more reserved words & symbols in Access)
-phone number (it is best not to have spaces or special characters in your table or field names)
-program
-role

tblAssessment
-assessmentid (primary key)
-staffid foreign key to tblStaff
-dteAssessment (date of assessment)

tblSkills
-pkSkillID primary key, autonumber
-txtSkillDescription
-fkSkillCategoryID foreign key to category table

tblAssessmentSkills
-pkAssessSkillID primary key, autonumber
-fkAssessmentID foreign key to tblAssessment
-fkSkillID foreign key to tblSkills

How do you know/identify which skills are the mandatory ones and which ones are not?

[LSHULSTER]

Sorry I didn't do the best job of describing my tables before. Here they are again

tblStaff (this describes the staff)

-StaffID (primary key)
-Pnumber
- Lname
- Fname
- JTitle
- Email
-Program
-Typeofuser

tblAssessment (one to many with tblStaff)
-AssessID (primary key)
-assessmentdate
-StaffID (foreign key)

tblSkills (one to many with tblAssessment)
-TransactionID (primary key)
-AssessID (foreign key)
-Category
-Skills


The two tables below contain the all the skills and how they are organized in categories.

tblSkillCategories
-CategoryID (primary key)
-CategoryName

tblSkillList (one to many with tblSkillCategories)
-SkillsID (primary key)
-skilldesc
-CategoryID (foreign key)


The mandatory skills are determined by the staff in each department. There are several types of staff in each department and they each might have different mandatory skills. Like the example below.

Department 1
Clerks (Mandatory skills)
skill1
skill2
skill3
Investigators (Mandatory skills)
skill1
skill2
skill3
skill4
skill5

Department 2
Clerks (Mandatory skills)
skill1
skill2
skill6
Investigators (Mandatory skills)
skill1
skill2
skill3
skill11
skill17

Thanks again so much for your time and help.



QUOTE=jzwp11;111583]If a skill can apply to many assessments and an assessment can have many skills that is a many-to-many relationship. Basically, that says that the skills should be in their own table

tblStaff (this describes the staff)
-staffid (primary key),
-name (note the word name is a reserved word in Access and should not be used as a table or field name; see this link for more reserved words & symbols in Access)
-phone number (it is best not to have spaces or special characters in your table or field names)
-program
-role

tblAssessment
-assessmentid (primary key)
-staffid foreign key to tblStaff
-dteAssessment (date of assessment)

tblSkills
-pkSkillID primary key, autonumber
-txtSkillDescription
-fkSkillCategoryID foreign key to category table

tblAssessmentSkills
-pkAssessSkillID primary key, autonumber
-fkAssessmentID foreign key to tblAssessment
-fkSkillID foreign key to tblSkills

How do you know/identify which skills are the mandatory ones and which ones are not?[/QUOTE]

[jzwp11]

The following table is not structured properly:

tblSkills (one to many with tblAssessment)
-TransactionID (primary key)
-AssessID (foreign key)
-Category
-Skills


You should not repeat the category again for the skill since you already define that in tblSkillList. The table should look like this:

tblSkills (one to many with tblAssessment)
-TransactionID (primary key)
-AssessID (foreign key)
-SkillsID (foreign key to tblSkillList)

The mandatory skills are determined by the staff in each department. There are several types of staff in each department and they each might have different mandatory skills. Like the example below.

Department 1
Clerks (Mandatory skills)
skill1
skill2
skill3
Investigators (Mandatory skills)
skill1
skill2
skill3
skill4
skill5

Department 2
Clerks (Mandatory skills)
skill1
skill2
skill6
Investigators (Mandatory skills)
skill1
skill2
skill3
skill11
skill17

To structure the above, you need more tables:

tblDepartments
-pkDeptID primary key, autonumber
-txtDepartmentName

tblJobTitles (i.e. clerk, investigator etc.)
-pkJobTitleID primary key, autonumber
-txtJobTitle

Now relate the job title to the applicable departments (assuming a department can have many job titles and a job title can occur in many departments as you indicated)

tblDepartmentJobTitles
-pkDeptJTID primary key, autonumber
-fkDeptID foreign key to tblDepartments
-fkJobTitleID foreign key to tblJobTitles

Now each job title within a department can have many mandatory skills

tblMandatorySkillsByJobDept
-pkMandatorySkillsID primary key, autonumber
-fkDeptJTID foreign key to tblDepartmentJobTitles
-fkSkillID foreign key to tblSkillList

[LSHULSTER]

Thanks alot. I'll try adding those tables and I'll see if it works.

The following table is not structured properly:

tblSkills (one to many with tblAssessment)
-TransactionID (primary key)
-AssessID (foreign key)
-Category
-Skills


You should not repeat the category again for the skill since you already define that in tblSkillList. The table should look like this:

tblSkills (one to many with tblAssessment)
-TransactionID (primary key)
-AssessID (foreign key)
-SkillsID (foreign key to tblSkillList)



To structure the above, you need more tables:

tblDepartments
-pkDeptID primary key, autonumber
-txtDepartmentName

tblJobTitles (i.e. clerk, investigator etc.)
-pkJobTitleID primary key, autonumber
-txtJobTitle

Now relate the job title to the applicable departments (assuming a department can have many job titles and a job title can occur in many departments as you indicated)

tblDepartmentJobTitles
-pkDeptJTID primary key, autonumber
-fkDeptID foreign key to tblDepartments
-fkJobTitleID foreign key to tblJobTitles

Now each job title within a department can have many mandatory skills

tblMandatorySkillsByJobDept
-pkMandatorySkillsID primary key, autonumber
-fkDeptJTID foreign key to tblDepartmentJobTitles
-fkSkillID foreign key to tblSkillList

[jzwp11]

I guess the next question would be how do you rate a person's proficiency at a skill during an assessment? I did not see a field in tblSkills for that purpose.