Query needed to solve candidate skills database

[dazza61]

Hi there,

The recruitment database I have designed for work is okay in the main except for where I need to filter candidates depending on their skill sets. What I need to be able to do is filter candidates that possess ALL skills selected in a multiselect listbox...

Table structure runs as: Candidates (many) linked to Job Role (one) (because candidates can only have one job title (in theory anyway)
Candidates linked to Skills table via junction table (candidate having many skills / one skill belonging to many candidates blah blah)...I'm sure this set up is as normalised as I can get (I aint no expert tho)...

My solution so far (suggested by another forum) was 3 listboxes on a form that runs like...
ListBox 1 = Job Role (Manager, Team Leader, Clerk, etc - set to SINGLE SELECT because an employee can only have ONE job title (supposed to anyway LOL)...
ListBox2= Skills (Payroll,Audit,Taxation, etc - SET TO MULTISELECT because employees can have more than one skill)...
ListBox3 = Candidates (populated by making selections in ListBoxes 1 and 2)

It all works well but is VERY slow as I was told to use make table, append queries and quite a bit of VBA to make the WHERE clauses as SQL statements, etc

I've searched high and low all over the net and have found things that come close but I'm just not adept enough to work it out (I started Access late in life). I feel I need some kind of subquery that first of all finds all candidates that e.g. have ALL 3 skills selected in Listbox 2 (creating a recordset of one row per skill meaning each candidate is listed in the recordset for as many skills selected and then filtering again with a count function that only displays candidates with a count of 3 skills - this subquery would then be used to populate Listbox 3 -

Sorry if I've overcomplicated this but it seems such a simple thing to and I'm getting a lot of pressure at work having being trying to solve this for weeks...

Any help putting me in the right direction or if you know of any similar example databases that would help me learn more would be much appreciated

Regards

dazza61

 

[Guus2005]

If you could post your database or lookalike i'll have a look at it.

 

[dazza61]

Candidate Skills Database

Thanks for getting back to me...

As requested, please find the attached db for you to have a look at...

Many thanks in advance

dazza61

 

[Guus2005]

The problem is not the query but the form design. You should change the two yellow listboxes to comboboxes. Then create a filter on which you need to run de green listbox. Your form should work a lot faster then.

HTH

 

[dazza61]

Thanks for the reply Guus...

Took your advice and re-designed the form - with 53 skills and counting I realised listboxes were not the way to go and the hideous make table, append routines were so cumbersome they are now a thing of the past...

So (with your advice) - I now use one Job Title/Role combo (Social Worker, Care Assistant, etc) and a maximum of 5 combo boxes (this is more than adequate - I checked with the directors) for the various skills which are attributed to each Job Role...(I could expand if need be though...)

I then designed a subroutine to check the state of each of the combos which qualified the correct query to run to filter the records...

Running with built in queries provided me with a lot of flexibility as I can select extra fields if necessary for more powerful searches...the list goes on...

The comparison is that instead of processing 100 records in 10-15 seconds - I can process 350 in a split second...(which also allows for expansion). It just goes to prove that sometimes it's best to scrap an idea (ditching your pride in the process) and take on a totally new one to get the best design for the job...

I haven't totally finished the design yet but instead of clouds, I see sunshine at the end of the tunnel

Many thanks, once again for your time in looking at this for me!!

dazza61