Results 1 to 2 of 2

Thread: Table Relation

  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Unanswered: Table Relation

    Hi,
    I have Three tables, Item(master Table), Item Ledger(ledger Table) & Value Entry(also a Ledger). How to relate these table in a query if i like to have data in the following manner,
    Item No. Description Quantity cost

    An Item Can Have many Item Ledger Rows(Quantity is Sum of Qty column on these rows) & An Item Ledger can have many Value Entry(the cost is sum of the cost column in Value entry.
    Lets Call Item Ledger as IL & Value Entry as VE

    The relation between these tables is Item. No_ = [IL.Item No_]
    and IL.[Entry No_] = VE.[ILEntry No_]

    For Example and Item ABC has 5 rows in IL table and each Rows have 2 VE rows respectively.
    IL
    Item No. Quantity Entry No.
    ABC 10 10001
    ABC 5 10010
    ABC 2 20093
    ABC 17 20095
    ABC 1 30005

    VE
    IL Entry No Cost
    10001 5.2
    10001 5.3
    10010 6
    10010 5.9
    20093 5
    20093 5
    20095 5.2
    20095 5.1
    30005 5.4

    The out should be like
    Item No. Description Quantity(SUM) Cost(SUM)
    ABC Test Item 35 48.1

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you provide the CREATE TABLE scripts and INSERT scripts to populate those tables?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •