BobRosas
asked on
Include only the most recent date in an already complex query.
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.ClientFileN o, "" AS CountyLkUpID, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkU pID, Count(dbo_tblClients.Clien tFileNo) 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.ClientFileN o = dbo_tblFunds.ClientFileNo
GROUP BY dbo_tblClients.ClientFileN o, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkU pID
HAVING (((dbo_tblFunds.FundFromDa te) Between ([Forms]![frmRptsByRange]! [txtFrom]) And ([Forms]![frmRptsByRange]! [txtTo]))) ;
I've tried MAX and Last on the FundFromDate but the ClientFileNo is still repeating.
Thanks in advance!
SELECT dbo_tblClients.ClientFileN
FROM dbo_tblClients INNER JOIN dbo_tblFunds ON dbo_tblClients.ClientFileN
GROUP BY dbo_tblClients.ClientFileN
HAVING (((dbo_tblFunds.FundFromDa
ASKER
Thank you for your quick response. I apologize if my request wasn't very clear but instead of showing each ClientFileNo once it only shows the first ClientFileNo. I'd like all records (which include many different ClientFileNo's) to show but have only the most recent date if the ClientFileNo repeats. I've made the changes you suggested and attached them to make sure I didn't mess something up but the code only produces 1 record.
Thanks again.
Thanks again.
SELECT TOP 1 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])))
ORDER BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate DESC , dbo_tblFunds.FundSourceLkUpID;
Can you send me the CREATE statement of your tables?
ASKER
I apologize for being at a loss. "CREATE statement"?
The tables in the query are from a SQL backend that I link to. Is that what you are asking? They are not make tables and I don't have any CREATE statements. Maybe that's why it's not working?
Thanks again!
The tables in the query are from a SQL backend that I link to. Is that what you are asking? They are not make tables and I don't have any CREATE statements. Maybe that's why it's not working?
Thanks again!
OK , never mind , I will handle it myself later .....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for sticking with me.
I tried the above code as is and got the error...
"You tried to execute a query that does not include the specified expression 'FundSourceLkUpID' as part of an aggregate function."
I tried leaving the field in the query but just unchecking it so it didn't show in the results and it will run but it only gives me one record again.
I thought that what I was trying to do was not that complicated and should work in one query but maybe not. I'm trying to see if I can make it work with 2 querys. Any input is appreciated.
Thanks!
I tried the above code as is and got the error...
"You tried to execute a query that does not include the specified expression 'FundSourceLkUpID' as part of an aggregate function."
I tried leaving the field in the query but just unchecking it so it didn't show in the results and it will run but it only gives me one record again.
I thought that what I was trying to do was not that complicated and should work in one query but maybe not. I'm trying to see if I can make it work with 2 querys. Any input is appreciated.
Thanks!
HI
I will need more info to proceed :
1) tables names + column names and types of each table
2) Primary key of each table
If you send me that (or the sql script that create those table), I will be able to test it and give you a complete solution.
Thanks
I will need more info to proceed :
1) tables names + column names and types of each table
2) Primary key of each table
If you send me that (or the sql script that create those table), I will be able to test it and give you a complete solution.
Thanks
ASKER
No need.
By separating into 2 queries and not grouping on dbo_tblFunds.FundSourceLkU pID until the 2nd query (after I filtered out the 1st record for each ClientNo), I was able to get the results I needed.
Thank you so much for all your help.
By separating into 2 queries and not grouping on dbo_tblFunds.FundSourceLkU
Thank you so much for all your help.
Open in new window