query help, MS ACCESS

small database with a table having fields NAME ABBREVIATION SCORE

Name is a longer term, abbreviation is its shorter term, score is a number

The names are unique in this table, the abbreviations are not

I have a query that pulls out a RecordSet of all the names that appear in a string of text, as well as their abbreviations and scores:

SQL = "select * from tblNamelist where '" & Replace(strText, "'", "''") & "' Like '%' + Name + '%'"

The hard part (for me, anyway)
without running a series of new queries, how can I add a new field to this query to indicate that this abbreviation has the highest score of all abbreviations that are identical to it? I assume a Yesno/Boolean/bit would be the field type.

To make the point clearer, suppose the data table looked like:
Name Abbreviation score
Jim Jones JJ 40
Joe Johnson JJ 30
Bill Smith BS 50
Bob Williams BW 20


So in a given string of text, Jim Jones appears. Currently my recordset would return with one line:
Jim Jones JJ 40
I would like one more field to indicate that there is no other JJ with a score higher than 40:
Jim Jones JJ 40 True

Otherwise, suppose Joe Johnson were in the string of text. Then it would return:
Joe Johnson JJ 30 False


Anybody?

Thanks.

wengang

It's probably not the best way, but I think this will work:

Code:

---

select * , score = (SELECT Max(score) FROM tblNamelist t2 WHERE t2.Abbreviation = tblNamelist.Abbreviation) from tblNamelist

---