Query Adding value from 2 different tables

[Marnelle]

Hello,

can somebody help me pls., i got stuck on this

[B]
TransactionTable1[/B]
   -date_of _transaction [I](date of transaction is compose of many items)[/I]
            
               item  quantity
                 A    30
                 B    35

[B]TransactionTable2[/B]
   -date_of_transaction [I](date of transaction is compose of many items)[/I]

              item  quantity
               A        30
               B        10

[B]Main_table[/B]

             item  Beginning_balance    deduction   ending-balance
               A        100             30+30=60        40
               B        100             35+10=45        55

deduction field from main_table is the sum quantity of transactioTable1and transactioTable2

 

[pbaldy]

You probably shouldn't have 2 transaction tables, but I would probably use a UNION query to pull the 2 tables into one data set and then a totals query to group/sum the data by item (and date if appropriate). That should give you your deduction amount.

 

[khawar]

Where does this beginning balance come from and if A is repeated more than once then you want sum of all qty or want to show each individual item