Hi all,
I'm having difficulties getting this query together.
I have a table cashflow containing several fields: ProjectID, Year, Cash_in Cash_out
Now if i have three records from One project eg:
IDprojectNr_FK, Year, Total_Cash_in, Total_CashOut
1, 2000, 200, 300
1, 2001, 400, 200
1, 2002, 400, 400
2, 2001, 2000, 3000
.. ...... ...... .......
Goal is to derive a Payback field that gives me the first year in which the project was payedback. So in the case of ProjectID 1 it would be year 2001
(200 + 400) > (300 + 200)
payback field = sum (Total_cash_in) >= sum(Total_cash_out)
but if i use the sum function it will calculate every record of IDprojectNR_FK 1
it has to stop where the criteria is met.
this is what i have so far:
SELECT tblCashflow.ID_ProjectNr_FK, Min(tblCashflow.Jaar) AS MinVanJaar, (Min([tblCashflow].[Jaar])-Min([tbl2].[jaar]))+1 AS payback
FROM tblCashflow, tblcashflow AS tbl2
WHERE (((tblCashflow.Total_cash_in)>[tblCashFlow].[Total_cash_out]) AND ((tbl2.ID_ProjectNr_FK)=[tblCashFlow].[ID_ProjectNR_FK]))
GROUP BY tblCashflow.ID_ProjectNr_FK, tbl2.ID_ProjectNr_FK;
But this does not work cause it checks if Total_cash_IN > Total_cash_out, Only per record!
and if i use the sum function then it checks it for every record. so that also does not work.
any ideas?
any help is appreciated.
Sincerely Yours,
D. Spangenberg
I'm having difficulties getting this query together.
I have a table cashflow containing several fields: ProjectID, Year, Cash_in Cash_out
Now if i have three records from One project eg:
IDprojectNr_FK, Year, Total_Cash_in, Total_CashOut
1, 2000, 200, 300
1, 2001, 400, 200
1, 2002, 400, 400
2, 2001, 2000, 3000
.. ...... ...... .......
Goal is to derive a Payback field that gives me the first year in which the project was payedback. So in the case of ProjectID 1 it would be year 2001
(200 + 400) > (300 + 200)
payback field = sum (Total_cash_in) >= sum(Total_cash_out)
but if i use the sum function it will calculate every record of IDprojectNR_FK 1
it has to stop where the criteria is met.
this is what i have so far:
SELECT tblCashflow.ID_ProjectNr_FK, Min(tblCashflow.Jaar) AS MinVanJaar, (Min([tblCashflow].[Jaar])-Min([tbl2].[jaar]))+1 AS payback
FROM tblCashflow, tblcashflow AS tbl2
WHERE (((tblCashflow.Total_cash_in)>[tblCashFlow].[Total_cash_out]) AND ((tbl2.ID_ProjectNr_FK)=[tblCashFlow].[ID_ProjectNR_FK]))
GROUP BY tblCashflow.ID_ProjectNr_FK, tbl2.ID_ProjectNr_FK;
But this does not work cause it checks if Total_cash_IN > Total_cash_out, Only per record!
and if i use the sum function then it checks it for every record. so that also does not work.
any ideas?
any help is appreciated.
Sincerely Yours,
D. Spangenberg