Help

[firoz.raj]

Sir ,

when i remove distinct keyword it also hang find attachment also
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

[QVeen72]

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

[firoz.raj]

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.

[QVeen72]

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

[firoz.raj]

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

[QVeen72]

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

[firoz.raj]

kindly find the atttachment same issue