Please Help

[Guest]

I have several transaction tables such as Purchasing, Transfer Accounts,
Sales that has AccountID on each one of them that tell where the money taken
from, or went to. How is the query and relationship like if I want it like:

AccountID SumOfSales SumOfPurchasing SumOfTransfer
Petty Cash $100 $30 $10
Bank $50 $0 $10
Petty Cash2 $0 $0 $0
Thank u so much for your help.

 

[Marshall Barton]

I have several transaction tables such as Purchasing, Transfer Accounts,
Sales that has AccountID on each one of them that tell where the money taken
from, or went to. How is the query and relationship like if I want it like:

AccountID SumOfSales SumOfPurchasing SumOfTransfer
Petty Cash $100 $30 $10
Bank $50 $0 $10
Petty Cash2 $0 $0 $0

I thik this may be what you want:

SELECT A.AccountID,
Sum(S.amount) As SumOfSales,
Sum(P.amount) As SumOfPurchasing
Sum(T.amount) As SumOfTransfer
FROM ((Accounts As A
LEFT JOIN Sales As S
ON A.AccountID = S.AccountID)
LEFT JOIN Purchases As P
ON A.AccountID = P.AccountID)
LEFT JOIN Transfers As T
ON A.AccountID = T.AccountID
GROUP BY A.AccountID