Results 1 to 3 of 3

Thread: help with query

  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Unanswered: help with query

    Hello,

    I'm trying to select the maximim value for certain zip codes, for example my table looks like this:

    ZIP Category Value
    90210 S1 5
    90210 S2 10
    90210 S3 2
    90210 S4 3
    10221 S1 24
    10221 S2 3

    And I want the end result to be each unique zip code with the category having the maximum value, for example 90210 S2, 10221 S1, etc

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You need an internal key on that table, it will make life easier. You could try something like this in the mean time:

    SELECT ZIP, Category
    WHERE [Value] = MAX([Value])
    GROUP BY ZIP
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Or you could have a query that shows each zip code and its maximum value.

    And then start another query based on joining the first query to the initial table joined on the zip code and max value fields.

    The concept is called subqueries.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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