-
Jan 19th, 2012, 04:34 PM
#1
Thread Starter
Frenzied Member
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
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Jan 19th, 2012, 05:42 PM
#2
Re: query help, MS ACCESS
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|