'Last' Non Null Record in Column

[Mpike926]

Okay so I have a column called 'totalincome' on TblCashflow. There are 60 records of TotalIncome (represents 5 years or 60 months) per asset that I have on another table, TblLoanGeneral.
What I need to return is the LAST value from totalincome that is not null. For example, we may only have entries in the 60 records that go to row #35, or 50 or whatever. When I use the Last criteria it gives me the 60th record (in example would give me a 0), and when I use the Max criteria, it gives me the largest income value regardless of location(would be 50,000 in example). What I need is really where the "last" income amt is >1 (20) however, when you set last, it only considers row 60. Thank you for the help


In the example below I would want 20 to be returned.

ID	Row#	TotalIncome
1	1	1000
1	2	2500
1	3	50000
1	4	20
1	5	0
1	6	0
1	7	0

[Mpike926]

Btw I understand that 7 is my max row #, i did not see the need to extend down to 60 but you guys get the point. Thanks!

[JamesDeckert]

I've done this and there's probably a slicker way, but the way I accomplished it was to have a query call a function which returned the value I was looking for.
the function does a query, in your case sorting by row# and Not [totalincome] is null.
In the resulting recordset I move to the last record and get the totalincome to return to the original query. The outer query is not necessary, so you may need it or not depending on where you want the info.

[DrGUI]

Try the following:

Select TotalIncome from TblCashFLow where [Row#] = (Select MAX([Row#]) from TblCastFlow where TotalIncome > 0)

[Mpike926]

even when I put Max in the row number it still returns all records of 60 regardless of the >0 criteria.
I am just trying to return these bolded amts. I dont know why this is giving me such a hard time. Its like there is no relation between month# ( I called it row # before) and the total income variable

ID	Borrower	TotalIncome	MaxOfMonthNumber
36706	Asset 1	700	3
36706	Asset 1	1400	5
36706	Asset 1	700	13
36706	Asset 1	700	14
36706	Asset 1	2100	15
36706	Asset 1	700	16
36706	Asset 1	80300	22
36693	Asset 2	437.5	2
36693	Asset 2	65000	36

[Mpike926]

Okay I can return the Max CashID from TblCashflow setting the criteria for income where >0. This gives me the CashID for the last income record. However, when I then add the TotalIncome to the query, it adds multiples, as each cash entry is unique. Does anyone know a way to query on the MaxCashID, and return only the total income that matches for that ID number? (Im thinking I could use what I have as a sub query to achieve what I am looking for?)

[DrGUI]

Yea I think a subquery would do the trick. You can try the following:

qry_GetMaxMonth:

SELECT ID, Max(MonthNum) AS MaxOfMonthNum
FROM tblCashFlow
WHERE [TotalIncome]>0
GROUP BY ID;

Then use this query to get the TotalIncome by ID:

SELECT [tblCashFlow].ID, [tblCashFlow].Borrower, tblCashFlow.[TotalIncome]
FROM tblCashFlow INNER JOIN qry_GetMaxMonth ON (tblCashFlow.ID=[qry_GetMaxMonth].ID) AND (tblCashFlow.MonthNum=[qry_GetMaxMonth].MaxOfMonthNum);

[Mpike926]

DrGUI, thanks for the help. I was able to call the ID for the last cashflow amt that I needed and then I just related on this ID with the cashflow table. Works like a charm.