Results 1 to 2 of 2

Thread: query help, MS ACCESS

  1. #1

    Thread Starter
    Frenzied Member wengang's Avatar
    Join Date
    Mar 2000
    Location
    Beijing, China
    Posts
    1,602

    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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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
  •  



Click Here to Expand Forum to Full Width