Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2007
    Posts
    26

    Unanswered: I give up! I need help with a select Max query

    The table tb_coolingtowers has 3 records 3 being the maximum but the following examples all come up with number 2

    SQL = "SELECT MAX([id]) FROM tb_coolingtowers;"

    SQL = "SELECT MAX (tb_coolingtowers.id) AS intid FROM tb_coolingtowers;"

    SQL = "SELECT MAX ([id]) AS intid FROM tb_coolingtowers;"

    SQL = "SELECT MAX ([id]) AS intid FROM tb_CoolingTowers ORDER BY Max([id]);"

    SQL = "SELECT MAX ([id]) AS intid FROM tb_CoolingTowers ORDER BY Max([id])asc;"

    I have run out of options HELP!


    Casey

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do us a favour and run the following query and show us the results:
    Code:
    SELECT id FROM tb_coolingtowers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    26
    Thanks R937
    The result is number 1

    casey

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So there is one row returned and that one row displays the number 1 - correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm confused. You have one record with an ID of 1 and your SQL shows you a 2 is the largest number and you look at the data and see 3 records?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2007
    Posts
    26
    Quote Originally Posted by pootle flump
    So there is one row returned and that one row displays the number 1 - correct?

    pootle flump yes number 1 is returned. but there are 3 records, I need to have number 3 returned

    casey

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So three records, all containing the number 1?

    BTW - can you also give me your definition of what MAX() means?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2007
    Posts
    26
    Quote Originally Posted by StarTrekker
    I'm confused. You have one record with an ID of 1 and your SQL shows you a 2 is the largest number and you look at the data and see 3 records?
    star trekker there are 3 records in the table, record no 1, record no 2 and record no 3, as no 3 is the max record no 3 is what I need to be returned, not record 1 or record 2

    I hope that clarifies the situation

    Casey

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry casey - forgive me but some of your terminology possibly is a bit "non-standard". Please could you just post a screen shot of the result?

    "Record no 1" <> "record with an ID of 1"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by casey.t
    Thanks R937
    The result is number 1

    casey
    are you sure? because it sounds like there are three rows, but you are saying that there is only one row?

    otherwise the query that i asked you to run will return three rows

    by the way, you will avoid a *lot* of confusion is you also mention why you want the number that you want -- what's it going to be used for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2007
    Posts
    26
    tb_CoolingTowers
    id Date Time NorthTower
    1 01/03/08 4:00 PM 263.507
    2 03/03/08 10:00 AM 265.024
    3 04/03/08 12:30 PM 266.312
    pootle fump
    Above are the records from tb_coolingtowers, id is a autonumber field
    as you can see there are not 3 number 1 but records 1, 2 and 3.

    I have never made a screen shot how do I do it ?

    Casey

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you don't need to make a screenshot, what you posted is very nice, thank you

    for testing purposes, would you please remove the row with id=2

    now, please tell us, what answer should your MAX query return now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2007
    Posts
    26
    R937

    tb_CoolingTowers
    id Date Time NorthTower
    1 01/03/08 4:00 PM 263.507
    3 04/03/08 12:30 PM 266.312

    with this code
    SQL = "Select * from tb_coolingtowers" it returnes number 1

    with this code
    SQL = "SELECT Max(tb_coolingtowers.id) AS intid FROM tb_coolingtowers;" it returns number 2

    Casey

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, i'm sorry, that is not correct

    the SQL that you are showing does not produce the results that you say

    something else is going on, either you are querying the wrong table, or connecting to the wrong database, or something

    note that SQL = "Select..." is not valid SQL, it is some kind of programming language

    please run your queries directly in the microsoft access SQL view

    and once again, i must ask you what you actually want and especially why (in a bit more detail please)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2007
    Posts
    26
    r937
    You are absolutely correct, it is pointing to the wrong copy of the database
    I have been taught a valuable lesson

    r937 thanks heaps

    Casey
    Last edited by casey.t; 03-05-08 at 08:08.

Posting Permissions

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