Justok,
>>Ok.. So, can I do SQL sth like:
>>Creaet SQL VIEW OrdersLeftQTY As ;
Select Orditems.Order_ID, Orditems.ITEM, Orditems.QTY - SUM(Shipment.SHIPQTY) as LeftQTY... ;
From Orditems Inner Join Shipment On Orditems.OrdIt_ID=Shipment.OrdIt_ID ;
Where OrdItems.Order_ID = ?Thisform.CurOrder_ID ;
Group By Shipment.OrdIt_ID
Well, you might want to change the "Inner Join" to "Left Join" to include Orditems that have NO shipments yet. The Inner Join of course would only give Orditems that have at least 1 shipment.
>>Then, I'd like to known the different btw :
>>Select .... ;
>>Group by Shipment.Order_ID, Shipment.ITEM
The above select would not necessarily give you a unique item. What if the order has 2 lineitems with the same ITEM. Which one does the shipment apply to?
>>and
>>Select .... ;
>>Group by Shipment.OrdIT_ID
This one ties the shipments directly to a particular line item only. No problem with several line items that have the same ITEM.
>>Or Using SUM, CALCULATE cmd to sum up value and subtract from Orditems.Qty?
Test it both ways and see how it performs. SQL Select, however is more portable if you move to SQL Server in the future. Either way, you need to have an ID on the lineitem which will be a foreign key in the shipment table.