Mix up few tables data ?

[Sogood]

i have few tables

table :sale, invoice, payment

all of them have :date , payby (cash/cheque) , currency

what should i do to join them(currency) up and group by month ?

or become this :

query - cash

___________| Cash |Invoice |Payment
2007 Jan____| $$$$$$ | $$$$$$ |$$$$$$
2007 Feb___| $$$$$$ | $$$$$$ |$$$$$$
so on

 

[jzwp22]

You would have a totals query for each table and then you would create another query that links the totals query by the year/month. Here is an example. You will have to substitute your own field names

qryPayments

SELECT year(tblPayments.transdate) & " " & monthname(month(tblPayments.transdate)) AS Period, Sum(tblPayments.transAmt) AS SumOfPaymentAmt
FROM tblPayments
GROUP BY year(tblPayments.transdate) & " " & monthname(month(tblPayments.transdate));

qrySales
SELECT year(tblSales.Saledate) & " " & monthname(month(tblSales.Saledate)) AS Period, Sum(tblSales.SaleAmt) AS SumOfSaleAmt
FROM tblSales
GROUP BY year(tblSales.Saledate) & " " & monthname(month(tblSales.Saledate));

qryInvoices
SELECT year(tblInvoices.Invdate) & " " & monthname(month(tblInvoices.invdate)) AS Period, Sum(tblInvoices.invAmt) AS SumOfInvAmt
FROM tblInvoices
GROUP BY year(tblInvoices.Invdate) & " " & monthname(month(tblInvoices.invdate));


This is the query that joins the 3 totals queries from above:

SELECT qryInvoices.SumOfInvAmt, qryPayments.SumOfPaymentAmt, qrySales.SumOfSaleAmt, qryInvoices.Period
FROM (qryInvoices INNER JOIN qryPayments ON qryInvoices.Period = qryPayments.Period) INNER JOIN qrySales ON qryInvoices.Period = qrySales.Period;