>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.
>

Thanks for all of you! I miss the non-shipment yet Orditems qty!


>>>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?
>

Sorry, in my project, Primary Key For OrdITEMS is Order_ID, ITEM.
Duplicate ITEM for same Order_ID is prohibited! So, I miss this case! >.<

>>>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.

---

Thank you, I will reconsider my DB structure as Rauh mension, One Shipment may consist serval orders with Same Customer!!
So, Shipment table will Break into Shipment and ShipLine to handle them!!
But I still use the above SQL to do the view...

I drop the SCAN..ENDSCAN method as time consuming greater then View Requery, and also need more code to maintain!