Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    San Rafael, CA
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts

    using two select statements in an update query

    I'm a newbie to access. Here's what I want to do. I want to update records with an update query equal to the sum of two expressions. The first expression is:
    UPDATE test_table SET test_table.test_rank = -(DCount("*","test_table","test_score >=" & [test_score])); --this runs fine by itself but is not the result I want--
    and add to it something like count(test_score)
    If I could combine this into one statement, it would give me the ranking of a list of values from smallest to largest. I just don't know how to get it into one update query statement. Please help

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by howardj1 View Post
    UPDATE test_table SET test_table.test_rank = -(DCount("*","test_table","test_score >=" & [test_score])); --this runs fine by itself but is not the result I want--
    and add to it something like count(test_score)

    You can put a + sign between two expressions, as long as you know what expressions you want to use. Something like

    UPDATE test_table SET test_table.test_rank = -(DCount("*","test_table","test_score >=" & [test_score]) + count(test-score));


    I don't know what the second expression should be because I don't know what you want to count.

    IS that a - sign in there? What is it for?
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    San Rafael, CA
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    John,
    I tried what you wrote but get the same error I have been getting "You tried to execute a query that does not include the specified expression '(the entire SQL you provided)' as part of an aggregate function". This does not happen if I replace the { COUNT(test_score) } piece with an integer, such as 11. Therein lies my problem.

    BTW, the reason there is a minus sign is that the first part of the function gives me the reverse of what I want, i.e. the highest test_score gets a "1", the next highest a "2" etc. I want the lowest test_score to get the "1", so I am making it a minus value, then plan to add in a number (slightly more complicated than my COUNT function) to make the order of test_scores run from low to high

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by howardj1 View Post
    John,
    I tried what you wrote but get the same error I have been getting "You tried to execute a query that does not include the specified expression '(the entire SQL you provided)' as part of an aggregate function". This does not happen if I replace the { COUNT(test_score) } piece with an integer, such as 11. Therein lies my problem.
    I was only saying that you can join two expressions with a +.
    I was not trying to tell you what the second expression should be, as I don't know what you are counting nor the logic of adding them together. In general you need to use DCount rather than Count. What about Dcount("*","test-table").

    BTW, the reason there is a minus sign is that the first part of the function gives me the reverse of what I want, i.e. the highest test_score gets a "1", the next highest a "2" etc. I want the lowest test_score to get the "1", so I am making it a minus value, then plan to add in a number (slightly more complicated than my COUNT function) to make the order of test_scores run from low to high
    Why not just change > to < in the first DCount?
    (DCount("*","test_table","test_score <=" & [test_score])
    Regards
    John



  5. The Following User Says Thank You to johnhutchison For This Useful Post:

    howardj1 (2011-03-22)

  6. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    San Rafael, CA
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    John,
    Thanks for the DCOUNT tip. Actually I figured that out myself subsequent to my most recent post.
    Regarding changing the >= to <=, I tried it out and it doesn't handle the tie scores correctly. Specifically If the 2nd & third lowest score were tied, I would want to scores to look like 1st, 2nd, 2nd, 4th, 5th etc. Your change has them come out 1st, 3rd, 3rd, 4th, 5th etc. Any ideas how to modify the DCOUNT statement to make it do this?

    To be completely honest, I'm not quite sure how the last part of this function even works (it was provided to me via internet research), so that's why I took the MINUS value, then added back in the number of affected records +1. That gave me the result I wanted, but if there is a way to get the formula to work without the additional part using another DCOUNT, that would be best, because the condition to count records gets tricky for me as the table grows. If you can think of a way to make the formula work as I need it to, please let me know.
    Howard

  7. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When I use this formula for rank: 1+DCount("*","tbltest","[score]<" & [score])
    Slightly different names than you use, I get this:
    rank.gif

    Is that what you want?
    Regards
    John



  8. The Following User Says Thank You to johnhutchison For This Useful Post:

    howardj1 (2011-03-23)

  9. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    San Rafael, CA
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Exactly what I wanted. I was going to try getting rid of the = sign as you did, only haven't gotten to it. Thanks so much for your assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •