Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    29

    Lightbulb Unanswered: Access pick the lowest number and place the column name into a field

    I have an Access Query with the following columns nams and data.

    DestZipCode,Origin44114,Origin90210,Origin44105,Op timalOrigin
    00501,2,5,2
    00544,3,5,1
    01003,2,2,5


    What I need to to is look into each row and find out the lowest number and place the column name into another field. If the lowest number has 1 or 2 values then have both column names in the OptimalOrigin field. I have 42,000 different zipcode.

    Example
    DestZipCode,Origin44114,Origin90210,Origin44105,Op timalOrigin
    00501,2,5,2,Origin44114 Origin44105

    Any help would be great!
    Thank You!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How about putting your origins in a seperate table and using a query to get the data you want. There are two "best practice" rules that are being violated here:

    1. Store like data in its own table.
    2. Do not store derived data.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Mar 2004
    Posts
    29
    I will get 31 different files with a different origin zip and time in transit I need to do a comparison to find the quickest delivery point per origin.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ohhhh...

    Well in that case, maybe you should try this:

    Quote Originally Posted by Teddy
    How about putting your origins in a seperate table and using a query to get the data you want.
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Mar 2004
    Posts
    29
    You are the Access Monkey I understand what you are saying to place the destorigin in fifferent tables but that does not tell me anything I am in need of help with a formula to find the lowest number in a row. Can you help me out?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I already did. Don't store then all in a row.

    If you used a schema like this:

    id
    zip_code
    origin
    destination
    rank

    Well, THEN I could help you fairly easily. If you keep the syntax the way it is, well now you have a very, VERY big problem on your hands... You will have to find a way to iterate through each field and compare it to every other field, not a simple task. In contrast, if you change your format, it would be as simple as:

    SELECT origin, rank
    FROM yourTable
    WHERE rank= Min(rank)
    GROUP BY zip_code




    I'm sure the regulars saw this coming... Read this.
    oh yeah... documentation... I have heard of that.

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

Posting Permissions

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