VBA Search Code

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
I have a table that contains jobs and competency requirements, 68 in all. Each job requiries different competencies. I've named the competencies: comp1 - comp 68.

I then have an employee table with competencies the employee possesses, they too vary and range from 1 - 68, also called comp1 - comp68.

I desperately need help writing a vba code that will run a loop to match competencies and return 3 top successors that match most or all of the position requirements.

:cry:
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Nancy

Do you mean you have 68 fields for the competencies?
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
I apologize I wasn't more clear.

Yes. the "critical position" table consists of fields for the position title, education, etc., and 68 fields for competencies. The "successors" table consists of various fields for last name, first name, education, etc., and 68 fields for competencies. There is also a competency look up table that lists the 68 competencies.

The critical position data entry form has a check box option for each competency required

The employee data entry form has a ranking option of either high, medium, or low.

My search needs to be conducted by selecting a critical position from a combo box that populates the selected position's specific data on the same form. (I've accomplished the combo box)

On that same form, I've created a button for the search process. I would need to rank the top 3 matching "High" competencies from the successors to the "Yes" requirements of the specific critical position selected from the combo box.

I'm very new to VBA and am somewhat confused as to when to use table names, etc., and how to write the statement. I'm also not sure if it could just be done by running a query and modifying the SQL with a top 3 command. My concern with the SQL is that this process won't be dynamic and any any new jobs or new employees won't be caught up in the search.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Nancy

You should not have 68 different fields for these competencies.

You should have 2 tables , 1 with employee details and one with competencies.

The employee detail table should hold only employee details and a unique primary key for each employee.

The competency table should hold a record for each competency an employee has.

It should have multiple records for each employee, which should be recognised using the unique primary key from the
employee detail table.

There should probably be a field in this table that indicates the level of ompetence.

I'm sorry but I can't explain further at the moment, I'm just about to go offline.
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
I can do this and understand what you are saying, but using your suggestion, wouldn't I actually need 3 tables: 1) critical positions, 2)competencies, 3) employee table?

For code, my tables are called: tblCriticalPositions, tblSuccessors, tblCompetencyLU.

The form that opens the selected critical position from a combo box is called: frmPotentialSuccessors. I would like a button to run the employee search against the selected position and return the value to the same form.

Thanks so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,337
Messages
5,641,571
Members
417,223
Latest member
jelena_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top