• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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.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])));
0
BobRosas
Asked:
BobRosas
  • 5
  • 4
1 Solution
 
elimesikaCommented:
try this
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
ORDER BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate, dbo_tblFunds.FundSourceLkUpID DESC
HAVING (((dbo_tblFunds.FundFromDate) Between ([Forms]![frmRptsByRange]![txtFrom]) And ([Forms]![frmRptsByRange]![txtTo])));

Open in new window

0
 
BobRosasAuthor Commented:
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.

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;

Open in new window

0
 
elimesikaCommented:
Can you send me the CREATE statement of your tables?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BobRosasAuthor Commented:
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!
0
 
elimesikaCommented:
OK , never mind , I will handle it myself later .....
0
 
elimesikaCommented:
sorry for the delay

Please check the follwoing and see what results you got , I have omitted the HAVING to see if it is the cause of the problem.
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
ORDER BY dbo_tblClients.ClientFileNo, dbo_tblFunds.FundFromDate DESC

Open in new window

0
 
BobRosasAuthor Commented:
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!
0
 
elimesikaCommented:
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
0
 
BobRosasAuthor Commented:
No need.
By separating into 2 queries and not grouping on dbo_tblFunds.FundSourceLkUpID 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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now