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.
Item No. Quantity Entry No.
ABC 10 10001
ABC 5 10010
ABC 2 20093
ABC 17 20095
ABC 1 30005
IL Entry No Cost
The out should be like
Item No. Description Quantity(SUM) Cost(SUM)
ABC Test Item 35 48.1