How to limit a query from showing duplicate values ...

[valkyry]

where there's a change in another field.

Ok, i have a query and I want it to NOT show the SKU and the information for that sku again IF the WHSE changes.

Here's what I currently have and not sure how to limit it to do the above.

SELECT NegativeOnHand.TransactionDate, IM2_InventoryItemWhseDetl.WhseCode AS FromWHSE, NegativeOnHand.ProductLine, NegativeOnHand.ProductType, NegativeOnHand.StdUM, NegativeOnHand.StdPrice, IIf([QOH]<[QtyNeg]*-1,[QOH]*-1,[QtyNeg]*-1) AS TnxQty, NegativeOnHand.StdCost, IM2_InventoryItemWhseDetl.QtyOnHand AS QOH, NegativeOnHand.WhseCode, NegativeOnHand.SKU, NegativeOnHand.QtyOnHand AS QtyNeg
FROM (NegativeOnHand INNER JOIN IM2_InventoryItemWhseDetl ON NegativeOnHand.SKU = IM2_InventoryItemWhseDetl.ItemNumber) INNER JOIN IM1_InventoryMasterfile ON IM2_InventoryItemWhseDetl.ItemNumber = IM1_InventoryMasterfile.ItemNumber
WHERE (((IM2_InventoryItemWhseDetl.WhseCode)="000" Or (IM2_InventoryItemWhseDetl.WhseCode)="005" Or (IM2_InventoryItemWhseDetl.WhseCode)="006") AND ((IM2_InventoryItemWhseDetl.QtyOnHand)>0) AND ((NegativeOnHand.WhseCode)<>"004") AND ((IM1_InventoryMasterfile.Category2)<>"Crosses" And (IM1_InventoryMasterfile.Category2)<>"Lamps")) OR (((IM2_InventoryItemWhseDetl.WhseCode)="000" Or (IM2_InventoryItemWhseDetl.WhseCode)="005" Or (IM2_InventoryItemWhseDetl.WhseCode)="006") AND ((IM2_InventoryItemWhseDetl.QtyOnHand)>0) AND ((NegativeOnHand.WhseCode)<>"004") AND ((IM1_InventoryMasterfile.Category4)<>"LCS"))
ORDER BY IM2_InventoryItemWhseDetl.WhseCode, NegativeOnHand.WhseCode, NegativeOnHand.SKU;

Your help would be greatly appreciated!!

 

[lespaul]

so you are saying that for each SKU there are multiple WHSE. which WHSE do you want to return?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[valkyry]

Hello,
There's a FromWHSE and WHSE (which is the ToWHSE)

For every ToWHSE is different for the same SKU and same TnxQty it should NOT show.

The result of the query shows, for example

FromWHSE SKU ToWHSE TnxQty
000 12345 009 2
005 12345 009 2
006 12345 009 2


the FromWHSE for SKU 12345 where it equals 005 and 006 since they are all to ToWHSE with the SAME TnxQty should not show.

hope that makes sense

 

[lespaul]

so if the query currently returns:

FromWHSE   SKU        ToWHSE    TnxQty
000        12345      009       2 
005        12345      009       2
006        12345      009       2

what do you want it to return?

 

[valkyry]

just the first one

FromWHSE SKU ToWHSE TnxQTY
000 12345 009 2


UNLESS the other's have a DIFFERENT qty

the SKU and ToWHSE and TnxQTY has to be the same for it NOT to show.

did that make sense?

thanks!

 

[PHV]

A starting point:
SELECT Min(FromWHSE), SKU, ToWHSE, TnxQty
...
GROUP BY SKU, ToWHSE, TnxQty

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

 

[valkyry]

ok - i wasn't thinking ...

that's for the kick start LOL!

I used the Min on the FromWHSE and First on the QOH and it seems to work.

I know these so I don't know why my brain wasn't thinking in on this one!!

thanks again for the reminder!!