Search multiple fields for single criteria

[whitfield77]

I have 3 fields; Skill1, Skill2, and Skill3

I want to ask the user to input a skill number to search for and then search all 3 of those fields for a match. I only want the user to enter the number once.

I'm sure this is something really simple, but I can't figure it out yet. Any ideas??

Thx.
Chris

 

[lespaul]

The simple answer is to normalize your database. As soon as you start naming fields with 1, 2, 3, etc. that's a certain flag that you have a one to many relationship and need a separate table to accomodate the relationship. For this situation you would have three tables, tblPerson, tblSkills, tblPeopleSkills.

tblPerson
PersonID (PK)
FName
LName
Address
etc.

tblSkills
SkillID (PK)
SkillName

tblPeopleSkills
PersonID
SkillID

and both PersonID and SkillID would be the PK for the tblPeopleSkills.

By having your table structured with skill1, skill2, skill3 what are you going to do with a person who has 4 skills? You're only option would be to add a skill# field each time someone gains more than 3 skills. With the normalized table structure above, any person can have ALL the skills in tblSkill without any table restructuring.

If you are not in a position that you can fix your table structure. You are going to need to create a form to collect the input of what skill you are looking for and use that entry as your criteria (the basic syntax will look something like this):

SELECT * FROM TABLENAME WHERE SKILL1 = frm!itemname or SKILL2 = frm!itemname or SKILL3 = frm!itemname

and then each time you have to add a skill to your table you will need to modify ALL the queries you have that use SKILL1, SKILL2, and SKILL3 to include the new skill field.

If you normalize your table you would need:

SELECT tblPerson.PERSONID, FNAME, LNAME FROM tblPERSON INNER JOIN tblPeopleSkills on tblPerson.PersonID = tblPeopleSkills.PersonID WHERE SKILLID = 1

will return the personid and name of all the people who have skillid #1. This can also be set up so the user selects the SKILL NAME.

For more information on data normalization see JeremyNYC's website:

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

HTH

Leslie

 

[whitfield77]

Here's what I currently have:

tblVector
VectorNumber (PK)
VectorName
Skill1
Skill2
Skill3
etc..

tblSkills
SkillNumber (PK)
SkillName
etc..

For what I am tracking, you can only have three skills. It is a technical limitation. I am the only one that uses this db, so I haven't done much in the way of forms, reports, etc..

You are saying that I should create a third, linking table:
tblVectorSkill
VectorNumber (PK)
SkillNumber(PK)

and then run the query against tblVectorSkill.SkillNumber

Thx.
Chris

 

[lespaul]

Yep! That's exactly what I'm saying. Makes it easier that this is just for you, but you'll be much happier in the long run if you normalize it now when it's still just yours!!

HTH

Leslie

 

[whitfield77]

Maybe I shouldn't have dropped Database Management halfway through. Right around the normalization chapter!



Thanks.
Chris