0

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

Attachments
2
Contributors
6
Replies
7
Views
8 Years
Discussion Span
Last Post by firoz.raj
0

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

0

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.

0

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

0

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

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.