Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Min figure Based on 2 sets of matching data

    Been struggling with this one for a while. Below is a table that I am working with. Its created by joining tables together.

    All the formats are numbers.

    I am looking to pick out the Row with the lowest distance with a matching PROD1 and ID. I have indicated by the asterix which record it should be.

    Although not shown here the PROD1 number does change through the 7,000 rows.

    I need for the update query which runs after this PROD1, ID and ProdLineCode.

    Prod1 ID ProdLineCode Distance
    100735 7 74 124
    100735 7 71 117
    100735 7 76 293
    100735 7 77 162
    100735 7 69 61 *
    100735 7 64 274
    100735 7 62 338
    100735 7 73 123
    100735 1 74 126
    100735 1 71 253
    100735 1 76 170
    100735 1 77 87 *
    100735 1 69 265
    100735 1 64 419
    100735 1 62 556
    100735 1 73 160
    100735 2 74 671
    100735 2 71 628
    100735 2 76 841
    100735 2 77 495
    100735 2 69 573
    100735 2 64 364
    100735 2 62 233 *
    100735 2 73 693


    Any help will be greatly appreciated.

    I am using Access 2007.

    Cheers

    Andy

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT Table1.Prod1, Table1.ID, Min(Table1.Distance) AS MinOfDistance
    FROM Table1
    GROUP BY Table1.Prod1, Table1.ID;
    will get you the minimum value fro Prod1 & ID

    you may need to use that as a subselect, and join on those columns AND prodlinecode to get the prodlinecode
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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