String/multiple word search 3

[Nene75]

Hi Everyone:

I would like to develop a search criteria where user can enter multiple words and results are found from more than one record.

For example:
I have a field called "Skills" where the data are:

1. Record1: MS Office, Visual Basic, Dreamweaver
2. Record2: Java, Visual Basic, Flash, MS Office, HTML
3. Record3: Photoshop, MS Office, MySQL, JavaScript

If the user is searching for MS Office, Visual Basic, JavaScript then all the 3 records would be displayed.

Is it possible to do it thru Query or SQL or VB? If yes please guide me how to build the search criteria to find the multiple word to search for in record(s).

Any help provided would be appreciated.

Thanks!

 

[Golom]

The critical part is the WHERE clause which would be something like

   WHERE    [Skills] LIKE '*Visual Basic*'
         OR [Skills] LIKE '*JavaScript*'
         OR [Skills] LIKE '*MS Office*'

If you are using ADO then replace the * character with %.

 

[Nene75]

Thanks Golom for the prompt reply.

How and where do I implement this code into query criteria?

 

[Golom]

Here's some skeleton code. I don't know your table structure or field names (other than "Skills&quot so I've invented some.

Select PersonID, Skills
From tblPersonSkills
WHERE    [Skills] LIKE '*Visual Basic*'
         OR [Skills] LIKE '*JavaScript*'
         OR [Skills] LIKE '*MS Office*'

 

[Nene75]

Golom:

I have the following in my query criteria:

Like &quot;*&quot; & IIf([Forms]![frmSearchCriteria]![Skills]<>&quot;&quot;,[Forms]![frmSearchCriteria]![Skills],&quot;*&quot; & &quot;*&quot & &quot;*&quot;

How do I implement your code in this criteria?

Thanks!

 

[Golom]

OK. In looking at this it appears that you have a text box ( [Forms]![frmSearchCriteria]![Skills] ) that contains ALL of the criteria that you want to search for but separated by commas. For example, the text box would contain

MS Office, Visual Basic, JavaScript

and you want to search for each of the three values even though they are in the same string. If you just plug that into your SQL without modification then none of your records in your original example would be retrieved because none of them contain &quot;MS Office, Visual Basic, JavaScript&quot; in exactly that order.

If you are committed to this approach then you will probably need to parse the text string using VB to extract the separate terms and then build the SQL statement based on the number of terms that were specified. The other possibility is to have separate text boxes (or whatever) where the user enters one search term in each and then values are plugged into the query from those user-supplied values as in the following example.

Select ...
From   ...
Where  [Skills] LIKE IIF ( [X1]="", "*", "*" & [X1] & "*")
    OR [Skills] LIKE IIF ( [X2]="", "*", "*" & [X2] & "*")
    OR [Skills] LIKE IIF ( [X3]="", "*", "*" & [X3] & "*")

I've abbreviated things like [Forms]![frmSearchCriteria]![Skills1] as [X1] to make the statement easier to read.

As to your last question, you really need to build statements like this using SQL view. Query builder is fine for simpler statements but becomes difficult to work with when conditional clauses become long and convoluted as this one does.

 

[lespaul]

In looking at your original post you have:

1. Record1: MS Office, Visual Basic, Dreamweaver
2. Record2: Java, Visual Basic, Flash, MS Office, HTML
3. Record3: Photoshop, MS Office, MySQL, JavaScript

are all those skills in a single field like you have listed above? or are they separate fields? or is this set up as a many to many relationship between employees and skills (EMPID + SKILLID = PK)?

Your table structure has a lot to do in determining how to write your query.

Leslie

 

[Nene75]

Golom:

Thanks for the prompt reply.

You're right what I have as a criteria which only retrieves record(s) if the search items are in that exact order with the comma.

I am using query approach because I have developed a custom search form where there are few text boxes and combo boxes where users can enter their search criteria. But Skills field can have more than one word to search for. As other text boxes and combo boxes are easy to implement with LIKE statement in query.

I have the following so far:

SELECT tblApplicantBioData.ApplicantFirstName, tblApplicantBioData.ApplicantLastName, tblApplicantEducationInfo.AppDegreeObtained, tblApplicantEducationInfo.AppDegreeMajor, tblWorkExperience.YearsOfTotalWorkExp, tblWorkExperience.YearsOfExpInJobAppliedfor, tblWorkExperience.GovernmentExp, tblApplicantEducationInfo.AppSkills, tblApplicantBioData.CurState, tblApplicantBioData.AppHomePhone, tblApplicantBioData.AppWorkPhone, tblApplicantBioData.AppCellPhone, tblApplicantBioData.AppEMailAddress, tblApplicantBioData.CurCity, tblResumeHistory.ResumeFile

FROM ((tblApplicantBioData INNER JOIN tblWorkExperience ON tblApplicantBioData.ApplicantID = tblWorkExperience.ApplicantID) INNER JOIN tblResumeHistory ON tblApplicantBioData.ApplicantID = tblResumeHistory.ApplicantID) INNER JOIN tblApplicantEducationInfo ON tblApplicantBioData.ApplicantID = tblApplicantEducationInfo.ApplicantID

WHERE (((tblApplicantBioData.ApplicantFirstName) Like &quot;*&quot; & IIf([Forms]![frmResumeSearchCriteria]![ApplicantFirstName]<>&quot;&quot;,[Forms]![frmResumeSearchCriteria]![ApplicantFirstName],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((tblApplicantBioData.ApplicantLastName) Like &quot;*&quot; & IIf([Forms]![frmResumeSearchCriteria]![ApplicantLastName]<>&quot;&quot;,[Forms]![frmResumeSearchCriteria]![ApplicantLastName],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((tblApplicantEducationInfo.AppDegreeObtained) Like &quot;*&quot; & IIf([Forms]![frmResumeSearchCriteria]![ApplicantLastName]<>&quot;&quot;,[Forms]![frmResumeSearchCriteria]![ApplicantLastName],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((tblApplicantEducationInfo.AppDegreeMajor) Like &quot;*&quot; & IIf([Forms]![frmResumeSearchCriteria]![AppDegreeMajor]<>&quot;&quot;,[Forms]![frmResumeSearchCriteria]![AppDegreeMajor],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((tblWorkExperience.GovernmentExp) Like &quot;*&quot; & IIf([Forms]![frmResumeSearchCriteria]![GovernmentExp]<>&quot;&quot;,[Forms]![frmResumeSearchCriteria]![GovernmentExp],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((tblApplicantEducationInfo.AppSkills) Like &quot;*&quot; & IIf([Forms]![frmResumeSearchCriteria]![AppSkills]<>&quot;&quot;,[Forms]![frmResumeSearchCriteria]![AppSkills],&quot;*&quot; & &quot;*&quot & &quot;*&quot AND ((tblApplicantBioData.CurState) Like &quot;*&quot; & IIf([Forms]![frmResumeSearchCriteria]![CurState]<>&quot;&quot;,[Forms]![frmResumeSearchCriteria]![CurState],&quot;*&quot; & &quot;*&quot & &quot;*&quot)
WITH OWNERACCESS OPTION;

Do I need to build SQL for other text boxes and combo boxes if I build SQL for Skills filed?

 

[Nene75]

Leslie:

'Skills' is a one field. There are more than one skills that Job Applicant can have.

I have four relational tables involved with this query.

 

[lespaul]

So you have a field named 'AppSkills' in tblApplicantEducationInfo, does the data in this field look like:

One record like this:
ApplicantID : 1234
AppSkills: MS Office, Visual Basic, Dreamweaver


or is it set up with multiple records per applicant like this:

ApplicantID : 1234
AppSkills: MSOffice

ApplicantID : 1234
AppSkills: Visual Basic

ApplicantID: 1234
AppSkills: Dreamweaver



Leslie

 

[Nene75]

Leslie:

One record like this:

ApplicantID : 1234
AppSkills: MS Office, Visual Basic, Dreamweaver

 

[lespaul]

Then part of the reason you are having so many problems is that this isn't normalized. If you can change the table structure to the second example you'll find that getting the information you need is much easier.



Leslie

 

[Nene75]

Leslie:

I already have one to many relationship with Applicant's Biodata table with Applicant's Education Information table. Skills field is part of Education information table. How do I normalize this tables so I can solve the issue of searching for multiple skills entered in the search criteria?

 

[lespaul]

You should create a new table called ApplicantSKills. This table should have:

ApplicantID
ApplicantEdInfoID
SkillID

This way each skill can be identified with their Education?

Personally, I would just have:

ApplicantID
SkillID

does it really matter where the skill came from in their Education? (but I don't know the details of your database or what you're using it for)

Leslie

 

[Nene75]

Leslie:

I am building a Resume Database that contains Applicant's Bio data, Education information, Work Experience, Work History, EEO information, Resume Submission/Revision history, Location Preferences, etc.

I had ApplicantSkills field as part of tblApplicantEducationInfo table. But you are saying that I should take out that field and make the table of its own and have many to many link from Education table to Skills table? Where currently Educataion table has link from ApplicantBioData table.

How should I sort this ...

 

[lespaul]

I had ApplicantSkills field as part of tblApplicantEducationInfo table. But you are saying that I should take out that field and make the table of its own and have many to many link from Education table to Skills table? Where currently Educataion table has link from ApplicantBioData table.

Sounds just about right. I would set it up like this though:

tblApplicantBioData with a one to many relationship with Education

and

tblApplicantBioData with a one to many relationship with skills

Leslie

 

[JOD59]

I'm not sure if this is what your looking for but I found it to be an excelent tutorial on SQL and drill down search

http://www.fontstuff.com/access/

 

[Nene75]

Golom, Leslie, 112804:

Thank you all for your interest and help on this topic. I'll work on this after the Christmas holidays. And will be back with more questions.

Happy Holidays!
Thanks!

 

[Nene75]

Happy New Year!

Hope you all had happy Holidays!

I was unable to break the skills into more than one field box. Users prefer to enter skills as part of the one text field with a comma seperator i.e. MS Office, Visual Basic, Dreamweaver, Access, etc. Can anyone help how should I sort this issue?

Any help provided would be appreciated.
Thanks!

 

[lespaul]

I would set it up so that the users can enter the information in a single cell separated by commas then when you are posting the information, parse that field into separate skills breaking it up at each comma. That way the users can enter the information any way they want, but you still get normalized tables that you can query easily. The other option would be to have check boxes for the most found skills so the user checks off the standard ones and have an &quot;Other&quot; field where they can enter the &quot;weird&quot; ones as a comma separated list.



Leslie