Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    16

    Unanswered: How Can I do this ?

    Hello All
    Please I need your help here
    I have 2 tables lets say the first is tbl_income(month,credit)
    month credit
    -------- --------
    March 1000
    May 3000
    November 9000

    The second table is tbl_outcome(month,debit)
    month debit
    --------- -------
    Feberury 3000
    March 1000
    April 4000
    May 1000
    November 2000
    December 5000

    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
    Last edited by lilana; 10-20-07 at 14:47.

  2. #2
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    lilana,

    You could create a query such as this:

    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...

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    lilana,
    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:
    Code:
    SELECT 
        tblMonths.Month, 
        Sum(tblIncome.Credit) AS SumOfCredit, 
        Sum(tblOutCome.Debit) AS SumOfDebit
    FROM (tblMonths 
        LEFT JOIN tblIncome 
            ON tblMonths.Month = tblIncome.Month) 
        LEFT JOIN tblOutCome 
            ON tblMonths.Month = tblOutCome.Month
    GROUP BY 
        tblMonths.Month, 
        tblMonths.MonthID
    ORDER BY 
        tblMonths.MonthID;
    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:

    January
    Feburary
    March......1000....1000
    April...................4000
    May.........3000....1000
    etc.

    Hope this helps

  4. #4
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    Vic: Good call! Wish I'd thought of that!

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Thanks Friz! I doubt if I would have thought enough about this situation if you hadn't given you answer which gave me the thought of the tblMonths. I love team work!

  6. #6
    Join Date
    May 2006
    Posts
    16
    WAW, thank you very much dear friends
    I really appriciate it for you

  7. #7
    Join Date
    May 2006
    Posts
    16
    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

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Lilana,

    Did the examples help you solve your problem? Or is something still not working?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •