Duplicate Item Numbers from query

[Former Member]

When I rune the following query, I get multiple item numbers even when I use the 'DISTINCT" clause. The query is here:

SELECT T1.[ItemCode], T1.[ItemName], T2.[FirmName], T1.[CardCode], T1.[SuppCatNum], T4.[ItmsGrpNam], T1.[U_U_CMG_PROGRAM], T1.[U_U_CMG_CATEG], T1.[U_CMG_STATUS], T1.[U_CMG_STORES], T1.[U_CMG_COLOR], T3.[OnHand], T1.[MinLevel], T1.[MinOrdrQty], SUM(T0.[OrderedQty]) AS Ord_Qty, SUM(T0.[PackQty]) AS Pack_Qty , SUM(T0.[Quantity]) AS Qty_Sold

FROM DLN1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OMRC T2 ON T1.FirmCode = T2.FirmCode INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OITB T4 ON T1.ItmsGrpCod = T4.ItmsGrpCod

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]

GROUP BY T1.[ItemCode], T1.[ItemName], T2.[FirmName], T1.[CardCode], T1.[SuppCatNum], T4.[ItmsGrpNam], T1.[U_U_CMG_PROGRAM], T1.[U_U_CMG_CATEG], T1.[U_CMG_STATUS], T1.[U_CMG_STORES], T1.[U_CMG_COLOR], T3.[OnHand], T1.[MinLevel], T1.[MinOrdrQty], T0.[OrderedQty], T0.[PackQty], T0.[Quantity]

How can I get my query to return unique values? Thanks for your help.

[Former Member · Accepted solution]

Hi Nate,

Gordon is correct, try to simplify your select statement.

The first thing I would try is remove the reference to the OITW table. As a general rule, by adding in the OITW table, you will get a row for each instance of Item and Warehouse, 10 WH means the Item will appear 10 times. Your query is slightly different as you are using the On Hand column, if you are using DISTINCT, you will get one row for each distinct instance of Item and On Hand quantity, so if you have 50 items On Hand in your main WH, 25 in a second WH, 25 in a third WH and the remaining 7 WH have 0, you will get 3 rows returned.

Item 1 - 50 On Hand

Item 1 u2013 25 On Hand

Item 1 u2013 0 On Hand

Regards,

Adrian

[Former Member]

Hi,

Basically, you can't include so many UDF there to group with. You have to delete all additional fields. Only keep the T1.ItemCode, T1.ItemName to start for summary. Then add field one by one to see if it fits in without so called duplicate.

Thanks,

Gordon