count occurences of searched words

[ale]

Hi!

I need to find all the records that contain word1 OR
word2 OR word3 - which I am able to do - and then to
order them in the following order: first the records that
contain word1 AND word2 AND word3, then the records that
contain two of the words, then the records that contain
just one word.
Any idea would be appreciated, thanx!

Ale

 

[Tom Ellison]

Dear ale:

You can add a column that gives a "score" for this. Perhaps 4 points
for matching word1, 2 points for matching word2, and 1 point for
matching word3.

IIf(SomeColumn LIKE "*word1*", 4, 0) +
IIf(SomeColumn LIKE "*word2*", 2, 0) +
IIf(SomeColumn LIKE "*word3*", 1, 0)

Once you get this working, sort by it DESC.

Please let me know if you get this working.

Hi!

I need to find all the records that contain word1 OR
word2 OR word3 - which I am able to do - and then to
order them in the following order: first the records that
contain word1 AND word2 AND word3, then the records that
contain two of the words, then the records that contain
just one word.
Any idea would be appreciated, thanx!

Ale

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[ale]

Dear Tom,
that's perfect, thanx a lot!
Ale

-----Original Message-----
Dear ale:

You can add a column that gives a "score" for this. Perhaps 4 points
for matching word1, 2 points for matching word2, and 1 point for
matching word3.

IIf(SomeColumn LIKE "*word1*", 4, 0) +
IIf(SomeColumn LIKE "*word2*", 2, 0) +
IIf(SomeColumn LIKE "*word3*", 1, 0)

Once you get this working, sort by it DESC.

Please let me know if you get this working.



Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[ale]

Dear Tom,

actually I have one more request: what if I want to
calculate the score as MAX( IIf(SomeColumn
LIKE "*word1*", 4, 0); IIf(SomeColumn LIKE "*word2*", 2,
0); IIf(SomeColumn LIKE "*word3*", 1, 0) )?
Can I do that?

Thank you!
--Ale