count occurences of searched words

  • Thread starter Thread starter ale
  • Start date Start date
A

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
 
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
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
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
Click to expand...
 
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
 
Back
Top