Gossamer Forum
Home : General : Databases and SQL :

Access Query!

Quote Reply
Access Query!
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