Please I need your help here
I have 2 tables lets say the first is tbl_income(month,credit)
The second table is tbl_outcome(month,debit)
my question is : How can I create a query to retreve credit and debit from both tables group by the month name like this
month credit debit
--------- ------- -------
Janeury ___ ___
Feberury ___ 3000
March 1000 1000
April ___ 4000
May 3000 1000
June ___ ___
July ___ ___
August ___ ___
September ___ ___
October ___ ___
November 9000 2000
December ___ 5000
SELECT tbl_income.month, Sum(tbl_income.credit) AS SumOfcredit, Sum(tbl_outcome.debit) AS SumOfdebit
FROM tbl_income INNER JOIN tbl_outcome ON tbl_income.month = tbl_outcome.month
GROUP BY tbl_income.month;
However, in order for it to display what you are looking for, you would first need to add each month at least once in each table, with a $0 value.
This is because the query is set up to join only where the month field appears in both tables... If you have a credit for January, but not a Debit for January, the query (in its current form) will not show any data for either.
The grouping option simply summarizes the credits/debits for each month. If the GROUP BY option was not set, and you had 3 credits/debits for March, March would appear 3 times in the query results... That *may* actually be what you're looking for, but wanted to give you some options.
Hope that helps...
Mods... Bear with me... first time poster... Not sure if there is something special I'm supposed to do with the code, so I simply cut & paste from the query window...
I would suggest, that rather than adding empty months to existing data tables, that you create a table that has as it's purpose to be used for reporting. In this table, all you need is one record for each month. That is, January, February, March, etc. You should also have a field that can be used for sorting the months in the correct order. That would be like a MonthID where January would be 1, Feburary would be a 2, etc. Then the SQL would look like this:
Sum(tblIncome.Credit) AS SumOfCredit,
Sum(tblOutCome.Debit) AS SumOfDebit
LEFT JOIN tblIncome
ON tblMonths.Month = tblIncome.Month)
LEFT JOIN tblOutCome
ON tblMonths.Month = tblOutCome.Month
What is happening here is the query will include all the records from tblMonths, and then any record from tblIncome and tblOutCome where the month matches the month in tblMonths. Here is what the output of that query will look like:
I like to clrify that above is an example to simplify what I need
but actully I have one table tbl_income(COA,credit) COA is the chart of account.
the second table is tbl_payments(project_ID,COA,paymentdue) it is 1:many relationship
project_ID COA paymentdue
---------- ----- -------------
1 1 1 1 1000
1 1 2 1 2000
2 3 3 1 10000
2 2 3 1 2000
2 3 3 3 2222
for each project there are many payments but maybe with diffrent COA
I just liked to explain that for you