Include only the most recent date in an already complex query.
Posted on 2008-06-09
Does any one have any suggestions on how I can filter the following query so that if the ClientFileNo repeats that only the record with the most recent FundFromDate is added to the make table?
I've tried MAX and Last on the FundFromDate but the ClientFileNo is still repeating.
Thanks in advance!
SELECT dbo_tblClients.ClientFileNo, "" AS CountyLkUpID, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID, Count(dbo_tblClients.ClientFileNo) AS Mth, 0 AS Target, 0 AS YTD, 0 AS 1stQtr, 0 AS 2ndQtr, 0 AS 3rdQtr, 0 AS 4thQtr INTO tblMTClientsServedRpt
FROM dbo_tblClients INNER JOIN dbo_tblFunds ON dbo_tblClients.ClientFileNo = dbo_tblFunds.ClientFileNo
GROUP BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID
HAVING (((dbo_tblFunds.FundFromDate) Between ([Forms]![frmRptsByRange]![txtFrom]) And ([Forms]![frmRptsByRange]![txtTo])));