Help

Hi,

You need to Remove the DISTINCTROW.
Any way, you are grouping on Product_id, you dont need a Distinct..
Try this Query for PartDetail:

Select Item_Code, ProductName, Max(ID) As MaxID, Sum(Qty) As MaxQty, Max(Unit) As ProUnit From Production Group By 
Item_Code, ProductName

Regards
Veena

Sir ,

why sum(qty), and max(unit)
since store keeper & data entry operator is working without knowing these things we can't change the views value.
kindly let me know.

Hi,

What is the structure of your Production Table...?
IF you have Multiple Production for same ItemCode
I guess, you need to take Sum(Qty),

Max(Unit) is Just to take a Unit, thats all
If unit is same for same product, then it should not make a difference

Regards
Veena

Sir ,
i have replace view :
SELECT PRODUCTION.Item_code, PRODUCTION.Productname, Max(PRODUCTION.ID) AS [Max Of ID], Sum(PRODUCTION.Qty) AS SumOfQty, Max(PRODUCTION.unit) AS MaxOfunit

FROM PRODUCTION

GROUP BY PRODUCTION.Item_code, PRODUCTION.Productname;

meanwhile recordset i opens like :

rs.Open " SELECT ITEM_CODE, PRODUCTNAME, UNIT FROM PARTDETAIL ORDER BY UCASE(LEFT(TRIM(ITEM_CODE),1)), VAL(MID(TRIM(ITEM_CODE),2,10)) ", con, adOpenDynamic, adLockOptimistic

same issue: system hangs when i run form

Hi,

Check if the Connection is Opened..
Change Your Query to :

rs.Open " SELECT ITEM_CODE, PRODUCTNAME, UNIT FROM PARTDETAIL Where Trim(Item_Code) <> '' ORDER BY 
UCASE(LEFT(ITEM_CODE,1)) & 
Format(Val(Mid(ITEM_CODE,2)),'000000000')  ", con, adOpenStatic, adLockReadOnly

Regards
Veena

kindly find the atttachment same issue