Query to Show the Exact Position of MaxValue in Other Column

[gstreichan]

Dears,

I am facing one issue that I think might be easy to solve but I am not getting it.

In 1 query I have 3 fields:

Item_Recv#
BOM_Pos
On_Hand

I want to show the max On_Hand quantity of every BOM_Pos followed by Item_Recv_# of the item as well.

For instance, on the screenshot example below, I need the query to show only the 2 rows below as 368.374 is the max of 4797 and 171 is the max of 4798 BOM Position. On top of that, the query should also show the correct Item_Recv_#, 67582 & 65793 respectively.

When I select First, Last, Min, Max for Item_Recv_#, it is always displaying the First, Last, Min, Max of the Item_Recv_# for the BOM_Pos and not the Item_Recv_# of the highest values.

Example below when choosing MaxOfItem_Recv_#

q022a_RRMRF_Select_auto q022a_RRMRF_Select_auto

BOM_Pos	MaxOfItem_Recv_#	MaxOfOn_Hand
4798​	68568​	171​
4797​	68609​	368.374​

Could you someone help how I work this around?

 

[plog]

You need a sub query to determine the max On_Hand of every BOM_Pos:

SELECT BOM_Pos, MAX(On_Hand) AS MaxOnHand
FROM YourDatasource
GROUP BY BOM_Pos

Save that as 'sub1'. Then make a new query using sub1 and YourDatasource, link them BOM_Pos to BOM_Pos and MaxOnHand to On_Hand. Bring in all the fields from YourDatasource that you want to see and that's your final query.

 

[ebs17]

SELECT
   D.BOM_Pos,
   D.[Item_Recv#],
   D.On_Hand
FROM
   (
      SELECT
         BOM_Pos,
         MAX(On_Hand) AS MaxOfOn_Hand
      FROM
         tblData
      GROUP BY
         BOM_Pos
   ) AS X
      INNER JOIN tblData AS D
      ON X.BOM_Pos = D.BOM_Pos
         AND
      X.MaxOfOn_Hand = D.On_Hand

SELECT
   D.BOM_Pos,
   D.[Item_Recv#],
   D.On_Hand
FROM
   tblData AS D
WHERE
   D.[Item_Recv#] IN
      (
         SELECT TOP 1
            X.[Item_Recv#]
         FROM
            tblData AS X
         WHERE
            X.BOM_Pos = D.BOM_Pos
         ORDER BY
            X.On_Hand DESC
      )

 

[arnelgp]

you may try to Remove MaxOfOn_Hand from q022a_RRMRF_Select_auto query and save the query.
next you need to create another query that joins your Original table and q022a_RRMRF_Select_auto query.

SELECT
    A.BOM_Pos,
    A.[MaxOfItem_Recv_#] As Item_Recv_#,
    B.On_Hand
FROM q022a_RRMRF_Select_auto AS A
INNER JOIN yourTableName AS B 
ON A.BOM_Pos = B.BOM_Pos AND A.[MaxOfItem_Recv_#] =  B.Item_Recv_#;

replace yourTableName with the correct name of your table.