Hi guys,
Hope someone can help/inspire me for an easier way to do this.
I've 2 tables. Table 1 being client's data and Table 2 being standard codes used
We have a standard set of job titles and its respective codes (e.g. CEO = "0001") assigned for standardization purpose. And companies have different job titles for the same job (e.g IT Helpdesk = IT Support = Support Helpdesk = IT analyst).
Whilst I rely on clients to code the jobs correctly according to our manual (table 2), some may be wrong/updated/outdated/not filled.
What I'm doing quarterly using the dump of 400,000 data lines is to filter jobs and assign the standard codes (e.g. filter for all CEO/Chief Executive/Chief Executive Officer and assign "0001" to it if it's not already "0001").
Is there a way to automate this process?
I'm thinking along the lines of creating a form to enter possible job title/keywords to search, with anything that matches these criteria get assigned a code in the job title column.
Or even if this is easier in Microsoft Access, do advice.
Thanks guys!
<tbody>
</tbody>
<tbody>
</tbody>
Hope someone can help/inspire me for an easier way to do this.
I've 2 tables. Table 1 being client's data and Table 2 being standard codes used
We have a standard set of job titles and its respective codes (e.g. CEO = "0001") assigned for standardization purpose. And companies have different job titles for the same job (e.g IT Helpdesk = IT Support = Support Helpdesk = IT analyst).
Whilst I rely on clients to code the jobs correctly according to our manual (table 2), some may be wrong/updated/outdated/not filled.
What I'm doing quarterly using the dump of 400,000 data lines is to filter jobs and assign the standard codes (e.g. filter for all CEO/Chief Executive/Chief Executive Officer and assign "0001" to it if it's not already "0001").
Is there a way to automate this process?
I'm thinking along the lines of creating a form to enter possible job title/keywords to search, with anything that matches these criteria get assigned a code in the job title column.
Or even if this is easier in Microsoft Access, do advice.
Thanks guys!
Table 1 | |||
Company | Job Title | Job Code | Level |
Company A | Accountant III | 15 | |
Company A | Junior Accountant | 13 | |
Company A | Senior Auditor | 15 | |
Company A | Junior Tax Accountant | 14 | |
Company B | Tax Analyst I | 13 | |
Company B | Financial Analyst I | 15 | |
Company C | CSR Advisor | 16 | |
Company C | Community Manager | 15 | |
Company C | Analyst, treasury | 15 | |
Company C | Mgr, Payroll | 15 |
<tbody>
</tbody>
Table 2 standard codes | ||
Job Title | Code | Level |
Accountant III | 1005 | 15 |
Accountant I | 1008 | 13 |
Auditor II | 1015 | 15 |
Tax Accountant I | 1021 | 15 |
Tax Analyst I | 1023 | 13 |
Financial Analyst I | 1028 | 15 |
Corporate Social Responsibility Specialist | 1843 | 15 |
Community Manager | 1844 | 15 |
Treasury Analyst | 5670 | 15 |
Payroll Manager | 5675 | 15 |
<tbody>
</tbody>