MaxDate From Query

[Mpike926]

Hi all, So for some background. I have a cashflow table that is a one to many relationship, and each ID on TblLoanGeneral has 60 records on tblcashflow.
There are 6 Income columns on TblCashflow. What I need is to pull the last dollar item from the cashflow for each record. I though I could just set the Cashflow date to max, however it pulls the very last record row 60 from tbl cashflow. I need the last AMOUNT that is entered into any one of the six cashflow columns
So for example
I tried using a union to 'stack' all six of the income columns and the max date from tblcashflow for each, and then I would from that be able to take the max cashflow for each record, but I cannot figure out how to do this.
From this example below, I would like just the Payoff Amt since it is the most recent event entered in the cashflow table. Right now I am returning Principal from May, as well as Interest from may and Payoff from August for each record. Just need august

ID	MONTH	PRINCIPAL	INTEREST	FEES	PAYOFF	NOTESALE	REOINCOME
7457	1/1/2014	500	250
7457	2/1/2014	500	250
7457	3/1/2014	500	250
7457	4/1/2014	500	250
7457	5/1/2014	500	250
7457	6/1/2014
7457	7/1/2014
7457	8/1/2014			50,000

Sorry if this is confusing, but basically I am looking for the last item of cashflow as this is our 'reversion' value...the value the final sale value of an asset.
Thanks!

btw my union query looks like this to get them all stacked. (the union is on [reversion]) which gives me the max of each of the columns, but I cant take the max of the max of that for some reason.


SELECT tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, Last(tblCashFlow.Principal) AS [Reversion], tblCashFlow.Strategy, Max(tblCashFlow.CFDate) AS MaxOfCFDate, tblLoanGeneral.PortfolioID
FROM tblCashFlow INNER JOIN tblLoanGeneral ON tblCashFlow.LoanID = tblLoanGeneral.ID
WHERE (((tblCashFlow.Principal) Is Not Null))


GROUP BY tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, tblCashFlow.Strategy, tblLoanGeneral.PortfolioID
HAVING (((tblCashFlow.Strategy)="recommended"))

UNION

SELECT tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, Last(tblCashFlow.Interest) AS [Reversion], tblCashFlow.Strategy, Max(tblCashFlow.CFDate) AS MaxOfCFDate, tblLoanGeneral.PortfolioID
FROM tblCashFlow INNER JOIN tblLoanGeneral ON tblCashFlow.LoanID = tblLoanGeneral.ID
WHERE (((tblCashFlow.Interest) Is Not Null))
GROUP BY tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, tblCashFlow.Strategy, tblLoanGeneral.PortfolioID
HAVING (((tblCashFlow.Strategy)="recommended"))

UNION

SELECT tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, Last(tblCashFlow.Fees) AS [Reversion], tblCashFlow.Strategy, Max(tblCashFlow.CFDate) AS MaxOfCFDate, tblLoanGeneral.PortfolioID
FROM tblCashFlow INNER JOIN tblLoanGeneral ON tblCashFlow.LoanID = tblLoanGeneral.ID
WHERE (((tblCashFlow.Fees) Is Not Null))
GROUP BY tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, tblCashFlow.Strategy, tblLoanGeneral.PortfolioID
HAVING (((tblCashFlow.Strategy)="recommended"))

UNION


SELECT tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, Last(tblCashFlow.Payoff) AS [Reversion], tblCashFlow.Strategy, Max(tblCashFlow.CFDate) AS MaxOfCFDate, tblLoanGeneral.PortfolioID
FROM tblCashFlow INNER JOIN tblLoanGeneral ON tblCashFlow.LoanID = tblLoanGeneral.ID
WHERE (((tblCashFlow.Payoff) Is Not Null))
GROUP BY tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, tblCashFlow.Strategy, tblLoanGeneral.PortfolioID
HAVING (((tblCashFlow.Strategy)="recommended"))

UNION

SELECT tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, Last(tblCashFlow.NoteSale) AS [Reversion], tblCashFlow.Strategy, Max(tblCashFlow.CFDate) AS MaxOfCFDate, tblLoanGeneral.PortfolioID
FROM tblCashFlow INNER JOIN tblLoanGeneral ON tblCashFlow.LoanID = tblLoanGeneral.ID
WHERE (((tblCashFlow.NoteSale) Is Not Null))
GROUP BY tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, tblCashFlow.Strategy, tblLoanGeneral.PortfolioID
HAVING (((tblCashFlow.Strategy)="recommended"))

UNION

SELECT tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, Last(tblCashFlow.REONOI) AS [Reversion], tblCashFlow.Strategy, Max(tblCashFlow.CFDate) AS MaxOfCFDate, tblLoanGeneral.PortfolioID
FROM tblCashFlow INNER JOIN tblLoanGeneral ON tblCashFlow.LoanID = tblLoanGeneral.ID
WHERE (((tblCashFlow.REONOI) Is Not Null))
GROUP BY tblLoanGeneral.ID, tblLoanGeneral.[MLS Number], tblLoanGeneral.Borrower, tblCashFlow.Strategy, tblLoanGeneral.PortfolioID
HAVING (((tblCashFlow.Strategy)="recommended"));

[John_G]

If I understand you correctly, you need the latest record where any one of the six columns is non-zero?

You probably need a sub-query, something like this:

Select * from tblCashFlow where ID = 7457 AND (principal > 0 OR Interest > 0 ....the other 4 here) and month in (select Max(month) from tblCashFlow where ID = 7457)

You might have to use aliases to distinguish between the two occurances of tblCashFlow :

Select A.* from tblCashFlow A where A.ID = 7457 AND (A.principal > 0 OR A.Interest > 0 ....the other 4 here) and A.month in (select Max(B.month) from tblCashFlow B where B.ID = 7457)

[Mpike926]

John thanks for the input. I was able to narrow down the fields using a set of subqueries with a union query compiling all of the subs. Right now I have the following union query which yields the following results

SELECT DISTINCTROW ReversionUnion.[MLS Number], ReversionUnion.Borrower, Max(ReversionUnion.CFDate) AS MaxOfCFDate, ReversionUnion.Reversion
FROM tblCashFlow INNER JOIN ReversionUnion ON tblCashFlow.LoanID = ReversionUnion.ID

GROUP BY ReversionUnion.[MLS Number], ReversionUnion.Borrower, ReversionUnion.Reversion;

Even when I try to narrow it down by throwing a Max( or Last( constraint on the date, It still gives me all values, how would I go about returning just the 10/31/2014 value for 16 river holding?

[John_G]

It's because you have included the "Reversion" in the Group By - all the "Reversion" values are different, so it gives you a separate row for each.

I'm not sure you need to you need to use Max and Group By here - I think you need a sub-query, and include the Max in that.

I haven't had time to test this:

SELECT  Alias_A.[MLS Number], Alias_A.Borrower, Alias_A.CFDate , Alias_A.Reversion
FROM tblCashFlow INNER JOIN ReversionUnion Alias_A ON tblCashFlow.LoanID = Alias_A.ID
WHERE Alias_A.CFDate = (select max(Alias_B.CFDate) FROM ReversionUnion Alias_B WHERE Alias_A.[MLS Number] = Alias_B.[MLS Number])

In this case, the Alises do have to be used.

[Mpike926]

So When I use a sub query I am able to narrow it down some, however, when I take the max of the CFDate for the record, it displays the max of the cfdate which is fine but it does not pull just one record. If there were 5 entries of principal for an asset, all five have the principal amt which are all different in $ value, but the cf date just shows the max for all five of the records. So I could have 10/50/75/80/100 in the principal column and when i strap the 'max' cf date to that, they all still show up but the max cf date is now just 1/1/2014 for all five of the records instead of just pulling the cashflow record associated with the 1/1/2014 date.
This is annoying -_-