Top or Max function question... 1

[miketheitguy]

Ok guys here's the deal..

I'm designing an inventory transfer module.
In one table are the goods to be transferred.
In the other table is the list of goods sorted by inventory qty, bin and warehouse.

So table A:

Item# Desc Qty
DP01090 Tortilla 10
DP01011 Corn Chip 23
Table B

Item# Warehouse Bin Onhandqty
DP01090 SEATTLE 2 10
DP01090 SEATTLE 13 154
DP01090 SEATTLE 1A 23
DP01090 SEATTLE 2C 500
DPO1011 SEATTLE 23 2
DPO1011 SEATTLE 45 300


My current query creates an inner join in the item # but it returns all the bins for each item. If I use MAX is still returns all the bins for each item and if I use top it returns only 1 bin, for one item.

What I want is a query that will return the bin with the highest onhandqty for EACH item in Table A, table A can have hundreds of items. Table B has a ALL Inventory Items so it's fairly large.

Any help...

 

[PHV]

Create a saved query, say qryMaxQty:
SELECT B.[Item#], B.Warehouse, B.Bin, B.Onhandqty
FROM [Table B] B INNER JOIN
(SELECT [Item#], Max(Onhandqty) As MaxQty FROM [Table B] GROUP BY [Item#]) M
ON (B.[Item#]=M.[Item#]) And (B.Onhandqty=M.MaxQty);
You can now join [Table A].[Item#] and qryMaxQty.[Item#]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miketheitguy]

I will very likely be naming my first born after you. And might I say... DUH to me. I'd been SOOOOO wrapped up in the freaking limitations of the Query grid designer in Access I let a basic SQL query just slip past me. You've calmed my ulcers and saved my hair line. Many thanks and may the gods smile on you... or.. you know.. whatever.