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

    Unanswered: Cash Flow Projection : Help me to do it

    Hello
    I have a table tbl_cash (Project_ID,Payment,Pay_Date) it is 1:many table

    Project_ID Payment Pay_Date
    ---------------------------------
    (1) $1000 21/1/2007
    (1) $2000 14/4/2007
    (1) $3000 30/7/2007
    (2) $2000 22/2/2007
    (2) $1000 15/7/2007
    (2) $4000 1/10/2007
    (3) $3000 24/4/2007
    (3) $5000 26/7/2007
    (3) $5000 23/10/2007
    (4) $2000 27/4/2007

    I need to create a Cash Flow Projection, like this

    Project_ID Januery Feburury March April May June ......etc.
    ---------------------------------------------------------------
    (1) $1000 $2000 $3000
    (2) $1000 $4000
    (3) $3000 $5000
    (4) $2000

    it is like grouping the sum of payments for each project according to the month of payment date
    can I create a table for cash flow and update it by these information

    Regards

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this for a specific year or all transactions?
    Will the number of column headings be fixed?
    George
    Home | Blog

  3. #3
    Join Date
    May 2006
    Posts
    16
    yes only one year 2008 for example

  4. #4
    Join Date
    May 2006
    Posts
    16
    coloums are fixed just 12 months for one year only

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Looks like a perfect candidate for a CrossTab Query. Use the Month and MonthName functions to convert the date to a month, then sum all the values.

    Here is the SQL:

    TRANSFORM Sum(tbl_Cash.Payment) AS SumOfPayment
    SELECT tbl_Cash.ProjectID
    FROM tbl_Cash
    GROUP BY tbl_Cash.ProjectID
    PIVOT MonthName(Month([PaymentDate]));

  6. #6
    Join Date
    May 2006
    Posts
    16
    Quote Originally Posted by DCKunkle
    Looks like a perfect candidate for a CrossTab Query. Use the Month and MonthName functions to convert the date to a month, then sum all the values.

    Here is the SQL:

    TRANSFORM Sum(tbl_Cash.Payment) AS SumOfPayment
    SELECT tbl_Cash.ProjectID
    FROM tbl_Cash
    GROUP BY tbl_Cash.ProjectID
    PIVOT MonthName(Month([PaymentDate]));
    ----------------------------
    Thank you v much DCKunkle
    I have 2 issues now
    the months not orderd :my way I tried to put in its normal place in the report
    missing months(no data) : my way I tried to add a project with ID = -1 with 0 payment for all months
    what you think ?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Sum(CASE WHEN month = 'january' THEN valueToBeSummed ELSE 0 END) As [Jan]
         , Sum(CASE WHEN month = 'february' THEN ...
    FROM theTable
    WHERE year = '2008'
    This might just do it...

    Note that there are functions that will return the datename (or integer value representing the month) from a datetime value - built into Access - just check the helpfiles
    George
    Home | Blog

  8. #8
    Join Date
    May 2006
    Posts
    16
    Quote Originally Posted by georgev
    Code:
    SELECT Sum(CASE WHEN month = 'january' THEN valueToBeSummed ELSE 0 END) As [Jan]
         , Sum(CASE WHEN month = 'february' THEN ...
    FROM theTable
    WHERE year = '2008'
    This might just do it...

    Note that there are functions that will return the datename (or integer value representing the month) from a datetime value - built into Access - just check the helpfiles
    ----------------------------------

    Thank you Georgev
    today I did it with v easy way
    I have created table tbl_cashmonth(Project_ID ,Jan ,Feb ,March April , ...) and I will put the payment in the sutable month.
    what you think ?
    but why I cant show the sum for every oloumn at the end of the report
    for example =sum(jan) it is give me an error message (#error)

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    With crosstabs you will have to make sure you have 0s in columns that don't have data. To do this you will want to use Nz in a second query to convert all Nulls to 0.

    You can also do it in the report. Instead of having a field bound to January, you can set the ControlSource to '=Nz(January,0)'. Then when you sum, you have to have the COntrolSource equal to '=Sum(Nz(January,0))'.

Posting Permissions

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