Data from three tables

[dref]

Hi there.
i have data from three tables namely: sales,expenditure & Purchases.
At the end of the day i want to balance and see how much money remains after all the three. how do i join these in a report as sales,expenditure,purchases and then balance. can someone upload an example for me?
1.Sales has Date,product,Amt
2.Expenditure has Date,Expense,Amt


3. Purchases has Paytdate,Product Name,Amtpaid
Fred

[maximus]

use DSum:

This can be done in a query as calculative field. Form Controls can be used as reference to make the Date criteria Dynamic.

the following expressions can also be used in the control source of unbound text boxes in a report.

Example:
TotalSales: Dsum("[Amt]","Sales","[Date]=#07/21/2010#")
TotalExpenditure: Dsum("[Amt]","Expenditure","[Date]=#07/21/2010#")
TotalPurchase: DSum("[Amtpaid]","Purchases","[PayDate]=#07/21/2010#")
Balance:TotalSales-(TotalExpenditure+TotalPurchase)

the following expressions can also be used in the control source of unbound text boxes in a report as
=Dsum("[Amt]","Sales","[Date]=#07/21/2010#") = TextBox1
=Dsum("[Amt]","Expenditure","[Date]=#07/21/2010#")=TextBox2
=DSum("[Amtpaid]","Purchases","[PayDate]=#07/21/2010#")=TextBox3

each in the control source of each unbound textBox
TextBox1-(TextBox2+TextBox3)

Note: text highlighted in red is not a part of the expression and is given to indicate which text box the expression is used.

[dref]

Thanks Maximus.
it has worked.
is there some code i code use to bring details of the same transactions such that i get a detailed report on sales,expenditure,purchases and balance in one query form or report?
Thanks
Fred

[maximus]

you need to use Subforms or SubReports.

please mark this thread solved as I feel your original problem is solved.