bedevilnslay
Registered User.
- Local time
- Today, 01:40
- Joined
- Jul 13, 2012
- Messages
- 11
Hi, I'm quite new to access and I'm building a query for HR people to find the right qualified person from range of data. Since there are 40-60 Qualifications depending on the employees's profile. The original employee database contains two tables. one is the general information with Staff ID as Primary key. and second tables contains all the information about qualification ID asscoated with Staff ID such as this table.
Staff_ID Qual_ID
1 1
1 2
1 3
2 1
2 2
2 4
3 2
3 3
4 2
4
I'm trying to generate a table in a form which list all people who have the right qualification based on user defined criteriia. For example I want to see a list of people contains Qualification ID 2 and 3. And it should come up a list with only two result which is Staff 1 and 3.
I realised that to achieve this I do need another table which has All the staff ID against each one of the qualification in order to run such query. Thus, I setup a table called tblEmployeeQualificaitonValidation with Staff_ID, Qual1, Qual2....Qual60.
And so, I create a Query which updates the First qualification field of tblEmployeeQualificaitonValidation to a True or False based on Employee ID and search whether first qualification exist in Employees's Qualification table. So the code is like this:
UPDATE tblEmployeeQualificationValidation INNER JOIN [tblEmployee Qualifications] ON tblEmployeeQualificationValidation.[Employee ID] = [tblEmployee Qualifications].[Employee ID]
SET tblEmployeeQualificationValidation.GEN001 = True
WHERE (([tblEmployee Qualifications]![Qualification ID & Description]) In ("GEN001"));
But I find it's difficult to update all the field (GEN001-GEN060) in one query. Do I have to build 60 queries and run with Macro. Or is there any way to build query into one with Switch, IIF or whatever possible function?
I'm kinda stuck right now. :banghead:Appreicated for any suggestion or help!!
Staff_ID Qual_ID
1 1
1 2
1 3
2 1
2 2
2 4
3 2
3 3
4 2
4
I'm trying to generate a table in a form which list all people who have the right qualification based on user defined criteriia. For example I want to see a list of people contains Qualification ID 2 and 3. And it should come up a list with only two result which is Staff 1 and 3.
I realised that to achieve this I do need another table which has All the staff ID against each one of the qualification in order to run such query. Thus, I setup a table called tblEmployeeQualificaitonValidation with Staff_ID, Qual1, Qual2....Qual60.
And so, I create a Query which updates the First qualification field of tblEmployeeQualificaitonValidation to a True or False based on Employee ID and search whether first qualification exist in Employees's Qualification table. So the code is like this:
UPDATE tblEmployeeQualificationValidation INNER JOIN [tblEmployee Qualifications] ON tblEmployeeQualificationValidation.[Employee ID] = [tblEmployee Qualifications].[Employee ID]
SET tblEmployeeQualificationValidation.GEN001 = True
WHERE (([tblEmployee Qualifications]![Qualification ID & Description]) In ("GEN001"));
But I find it's difficult to update all the field (GEN001-GEN060) in one query. Do I have to build 60 queries and run with Macro. Or is there any way to build query into one with Switch, IIF or whatever possible function?
I'm kinda stuck right now. :banghead:Appreicated for any suggestion or help!!