>Hi all
>
>I have this requirement where I have to maintain a Balance report between 2 tables. The concept is such...

I don't quite understand what the question is - is it whether we think your table structure is appropriate?

>
>There can be multiple Sale Order Line Items for a given Item form the same party. ie. Mr. X can order Item A not just once but can place repeated orders while the previously orders are still being despatched.
>
>eg.
>1. SO no. 1 Dt. 01/05/2003 Mr. X orders Item A qty. 500 Item B qty. 1000
>2. SO no. 2 Dt. 03/05/2003 Mr. Y orders Item B qty. 250
>3. I no. 1 Dt. 06/05/2003 Despatched Item A qty. 300 (SO no. 1) and Items B qty. 300 (SO no. 1) to Mr. X
>4. I no. 2 Dt. 07/05/2003 Despatched Item B qty. 50 (SO no. 2) to Mr. Y
>5. SO no. 3 Dt. 10/05/2003 Mr. X orders Item A qty. 750
>3. I no. 3 Dt. 15/05/2003 Despatched Item A qty. 500 (200 SO no. 1 + 300 SO no. 3) and Items B qty. 300 (SO no. 1) to Mr. X
>
>
>I have to maintain a SO no. wise track of despatches like
>
>SO no. 1 Mr. X Item A 500
>--- I no. 1 300
>--- I no. 3 200
>
>SO no. 1 Mr. X Item B 1000
>--- I no. 1 300
>--- I no. 3 300
>
>
>Tables:
>

>1. An Sale Order Header Details Table (tSaleOrder)
>      iID        Int   Unique ID    Primary Key
>      iNo        Int   Order No.    Candidate Key
>      dDt        Date  Order Dt.
>      iPartyID   Int   Party ID
>
>2. An Sale Order Items Table (sSaleOrderItems)
>      iID        Int   Unique ID    Primary Key
>      iPID       Int   Order's ID
>      iItemID    Int   Item ID
>      yQty       Curr  Quantity
>
>3. An Invoice Header Details Table (tInvoice)
>      iID        Int   Unique ID    Primary Key
>      iNo        Int   Invoice No.  Candidate Key
>      dDt        Date  Invoice Dt.
>      iPartyID   Int   Party ID
>
>4. An Invoice Items Table (sInvoiceItems)
>      iID        Int   Unique ID    Primary Key
>      iPID       Int   Invoice's ID
>      iItemID    Int   Item ID
>      yQty       Curr  Quantity
>
>5. Despatches Mediator Table (uOrdInvClr)
>      iID        Int   Unique ID    Primary Key
>      iPID       Int   Invoice's ID
>      iSID       Int   Order's ID
>      iItemID    Int   Item ID
>      iPartyID   Int   Party ID
>      yQty       Curr  Quantity
>      cPDBF      Char  Parent DBF's name for future expansion ?!?
>      cSDBF      Char  Source DBF's name for future expansion ?!?
>      cPFld      Char  Parent DBF's Field name for future expansion ?!?
>      cSFld      Char  Source DBF's Field name for future expansion ?!?
>