A Searching Problem

[Donall]

I have been developing a Access 97 database and come to a problem that I hoped someone may be able to help me with.

I have a table of people, (PersonID, name, address) and for each record I have a one to many relationship with another table containing information of these peoples skills (SkillID, PersonID, skill)

Now I want to search my database to find all the people who have a particular set of skills which is entered into a form.

I know, it sounds simple, but because I don't want to limit the number of skills that I am searching for at any time I have reached the limits of my ability to write queries. If there is anyone who can help me out on this one I would be very grateful.

[andi_g69]

I assume users enter skills they are searching for as free text? E.g. you get a string from your form like this:

sSearch = "Database Design, HTML, ASP"

and your skills table holds records:

SkillID, PersonID, Skill
1, 1, "HTML"
2, 1, "ASP"
3, 2, "Database Design"
4, 3, "Database Design"
5, 3, "ASP"
6, 3, "C++"
7, 4, "Natural"
You query could look like this:

SELECT DISTINCT
p.PersonID, p.Name
FROM Persons p
inner join Skills s on s.PersonID = p.PersonID
WHERE "%" + s.Skill + "%" LIKE sSearch

[Donall]

Thanks for that, however, my problem becomes more complicated as I want to search for more than one skill at a time.

For every person they can have any number of skills, and when I search I want to find those people who have all the skills I am looking for. eg. all those people who are database designers and can program in C++ and Java.

I had it working when I limited myself to searching for just three skills by creating a query with my people table joined to my skill table three times. However I don't know how to do this when I don't know how many skills I am searching for. I'd like the user to be able to search for up to about 20 skills and so I could extend my three skill approach to 20 but this is very big and complicated and I would like to believe there is a more elegant solution.

Thanks for your time, I look forward to any suggestions.

[PinkPanther2003]

If you want multiple selections the only way I know how to do it is in code.

Use a multi select list box and convert each selected item (see the ItemsSelected collection of the list box in Access's help) into a string such as

'start code
dim strSQL as string

For Each varItem In rctlListBox.ItemsSelected
if len(strSQL)=0 then
strSQL = varitem
else
strSQL = varitem & "," & varItem
end if
Next

strSQL = "S.SkillID IN(" & strSQL & ")"
'end code

wrap it in a function for reuse.

"S.Skills IN('VB','SQL','C++')"

OR "S.SkillID IN( 1,2,3,4 etc)"



Looking at your table design
(SkillID, PersonID, skill) - this is incorrect usually. Skill depends on the SkillID - so it should be in a seperate table

So

tblPersons
PersonID
Address
FamilyName
etc

tblSkills
SkillID
SkillName
etc

tblPersonsSkills
PersonID
SkillID


What you are modelling is a many-to- many relationships between people and skills.

HTH,

Peter

[Donall]

Thank you very much for this. This is something I was thinking about and I am working on a bit of code that might do what I'm looking for. The big problem is that I need to search for people with all the skills I specify and your use of the IN clause appears to give me people who have one or more of the skills specified. (OR function as opposed to AND function)

I am now thinking of using your loop to build an SQL statement that I can then run with a DoCmd.RunSQL statement.

Before I started this thread I had thought that it may be possible to start with the full recordset of people, then repeatedly loop through a series of queries which delete any people who do not have the skill I am looking for. Unfortunately I am not sufficiently familiar with VBA or SQL to write this. Is it possible to create a temporary recordset that can be repeatedly queried in a loop? This would mean that I am continually reducing the date being queried which should mean it is still reasonable efficient for large amounts of data.

If anyone can help me out in writing this piece of code I will be extremely grateful.

In the mean time I will try write something that will create an SQL statement to complete the search in one go and as soon as I have something I'll publish it here.

Your comments on my table configuration is perfectly correct and I have implemented this.

Thank you for your time and interest.

[PinkPanther2003]

Personally I think you're barking up the wrong tree with docmd.runsql

Also temporary tables/recordset and 'looping through' recordsets (read cursors) is expensive in terms of time.

Why try to arrive a results set by removing what you don't want? Try first to just ask for what you do want.

The idea behind a search form is you build up the criteria on the search form and then click a command button and it uses the values in the search form controls to populate another form displaying the details of the records matching your search criteria.

There are numerous ways to do this.

1. Reference the search controls in the recordsource of your display form

eg WHERE (S.SkillID = Forms!frmSearch!cboSkills) OR|AND etc....

2. Use VBA to build up an SQL string and then assign this string via

forms("display form").recordsource = <your sql string>

3. Use VBA to change the filter property of the display form and then

forms("display form").FilterOn = True

4. Use docmd.openform ,,,,<your where clause> as string,,

There may be more....

Using a multi select listbox generally reduces you options to a code based one.

Just my 200000 turkish lira's worth,

HTH,

Peter

[PinkPanther2003]

Oh, BTW Docmd.RunSQL doesn't work with SELECT statements, only 'action queries' (eg DELETE, INSERT, UPDATE, CREATE, ALTER)

[rwendel]

Using the SQL statement from above, if you want a list of people with any of the skills listed, in order of people with the most skills at top, then select, like above, and group by person, order by COUNT( skills ) DESC.

I haven't tried this, but maybe it will help, b/c I assume you are trying to find people who MOST match the criteria given, but if there are no exact matches, get the ones that are closest.

[Donall]

I started this thread as this whole area of SQL and VBA is relatively new to me and all the discussions to date have been operating very close to my boundary of ignorance on the subject. So much so I'm not 100% sure I understand the solutions proposed so far and so the solution I am about to propose may well be viewed as completely the wrong thing to do but having tested it very briefly it does appear to work.

I've used Peters suggestion to develop a SQL string in VBA and the string itself is similar to the query I had developed myself ages ago when I was limiting the skills I was looking for. I have used a DoCmd.RunSQL command as I had been using an action query and I don't know any other way to run an SQL statement from within VBA.

Any why here is:

Section1 = "INSERT INTO Shortlist ( CanID, VacID )SELECT Candidate.CanID, [Forms]![Search]![VacID] AS VacID FROM"
Section3 = "Candidate "
Section5 = " WHERE "
Section7 = " GROUP BY Candidate.CanID"
DoCmd.GoToRecord , , acLast

For i = Me.CurrentRecord To 1 Step -1

Section2 = Section2 + "("
Section4 = Section4 + "INNER JOIN CanSkill AS CanSkill_" + Format(i) + " ON Candidate.CanID = CanSkill_" + Format(i) + ".CanID)"
Section6 = Section6 + "CanSkill_" + Format(i) + ".SkillID = " + Format(Forms![Search]![SearchSkill]![SkillID])
If i <> 1 Then
Section6 = Section6 + " AND "
DoCmd.GoToRecord , , acPrevious
End If
Next
DoCmd.RunSQL Section1 + Section2 + Section3 + Section4 + Section5 + Section6 + Section7

Let me explain: people records are stored in a table called Candidate. Their skills are stored in a table called CanSkill. I am storing the results of the search in a table called Shortlist.
This code is run form a button which is located on a continuos subform which holds the SkillID for the skills to be searched for forms![Search]![SearchSkill].
I am looping backwards through the records as there is some redundancy in the skills listed and I can add to this code to improve its performance.

I am still concerned that with a lot of skills to search for and a lot of people in my database this may not be the best way to achieve my goal.

If anyone would like further explanation or as any comments or can suggest any better ways to do this then please reply.

Thank you all for your assistance to date.