Solved

Include only the most recent date in an already complex query.

Posted on 2008-06-09
9
154 Views
Last Modified: 2010-03-20
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
Comment
Question by:BobRosas
  • 5
  • 4
9 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 21746830
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
 

Author Comment

by:BobRosas
ID: 21746937
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
 
LVL 19

Expert Comment

by:elimesika
ID: 21748925
Can you send me the CREATE statement of your tables?
0
 

Author Comment

by:BobRosas
ID: 21752141
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 19

Expert Comment

by:elimesika
ID: 21752173
OK , never mind , I will handle it myself later .....
0
 
LVL 19

Accepted Solution

by:
elimesika earned 250 total points
ID: 21776429
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
 

Author Comment

by:BobRosas
ID: 21780534
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
 
LVL 19

Expert Comment

by:elimesika
ID: 21786287
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
 

Author Comment

by:BobRosas
ID: 21794482
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
monitor queries that use too much tempdb log 20 42
Getting certain data from a string 1 36
the whoisactive update 12 39
email about the whoisactive result 7 27
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now