Created a query that brought back all the loan values in approx. 25 columns. Now the reqest is to bring back the max value from that range of columns/fields. That wasn't a problem. The problem is now they want the next 3 lower values to show up for a range of accounts. Here is what I used to get the max number. How then can I build another column of data that will show the next highest loan value in that range of cells?
SELECT Property.PROPNAME, Max([SBEGIN]*-1) AS [Master Loan]
FROM Property INNER JOIN (Total INNER JOIN Acct ON Total.HACCT = Acct.HMY) ON Property.HMY = Total.HPPTY
WHERE (((Acct.SCODE) Between "25050000" And "25570000") AND ((Year([umonth]))=Year(Now())) AND ((Month([umonth]))=Month(DateAdd("m",0,Date()))))
GROUP BY Property.PROPNAME;
Thanks,
Chester
SELECT Property.PROPNAME, Max([SBEGIN]*-1) AS [Master Loan]
FROM Property INNER JOIN (Total INNER JOIN Acct ON Total.HACCT = Acct.HMY) ON Property.HMY = Total.HPPTY
WHERE (((Acct.SCODE) Between "25050000" And "25570000") AND ((Year([umonth]))=Year(Now())) AND ((Month([umonth]))=Month(DateAdd("m",0,Date()))))
GROUP BY Property.PROPNAME;
Thanks,
Chester
