Finding a Match

[Guest]

Hello,

I have a table:
empID
interestID
empInterests

where this table contains a list of interests for each employee.
So, for example,
empID interestID empInterests
---------------------------------------------------
1 1 car
1 2 books

Now, the query I want to make should be able to match the pattern "cars"
with the first record; it should also match the pattern "book" with the
second record.
Basically, a match should be returned if the word in the 'empInterests'
field is found in the pattern or the word in the pattern is found in the
'empInterests' field.

I tried the 'like' operator, but it did not work for me the way i want it to.

Please help

 

[Guest]

I see a couple of problems. You never can get a match where you seek a
plural (cars) in a field that has singular (car).
Your 'book' can be found in 'books' using this as criteria --
Like "*" & [Enter search item] & "*"
It will also find 'booking' when you enter 'book.'

I recommend you revise your table structure to have a one-to-many
relationship between a table named Interest and your EmpInterest table.
Interest ---
InterestID - Autonumber - primary key
Interest - text ( use singlar generic items in the list)

EmpInterest ---
EmpID - number - integer - foreign key - related to [Employee].[EmptID]
InterestID - number - integer - foreign key - related to
[Interest].[InterestID]