SQL Query suddenly stops working?

[jpstroud]

I have an SQL query that I use to determine who is the director (or acting director) of a particular agency. The person with the lowest TeamID then lowest Ranking is who's in charge currently (this takes into account agencies who are between directors). Here is the current query:

SELECT L.*
FROM dbo_LDPE_Person AS L INNER JOIN [SELECT LDPE_LibraryID, Min(LDPE_TeamID+100*LDPE_Ranking) As MinRankTeam FROM dbo_LDPE_Person GROUP BY LDPE_LibraryID] AS M ON (L.LDPE_TeamID+100*L.LDPE_Ranking=M.MinRankTeam) AND (L.LDPE_LibraryID=M.LDPE_LibraryID)
WHERE LDPE_PersonType="Library Staff";
[code]

Unfortunately, yesterday morning I was working on another query that involved this one, and now neither query works. I didn't make any changes to the database itself, I was just doing a SELECT.

Any thoughts?

I've gone over it multiple times, unless I've really missed a typo, all the names are correct.  And again, it was working fine.

Thanks!

 

[PHV]

Replace the [....]. by (....)
Or create a saved query named, say, qryGetMinRankTeam:
SELECT LDPE_LibraryID, Min(LDPE_TeamID+100*LDPE_Ranking) As MinRankTeam
FROM dbo_LDPE_Person
GROUP BY LDPE_LibraryID;

And modify your above query like this:
SELECT L.*
FROM dbo_LDPE_Person AS L
INNER JOIN qryGetMinRankTeam AS M ON (L.LDPE_TeamID+100*L.LDPE_Ranking=M.MinRankTeam) AND (L.LDPE_LibraryID=M.LDPE_LibraryID)
WHERE LDPE_PersonType="Library Staff";


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jpstroud]

Thanks PHV. I'm still fairly new to SQL. After being frustrated by this, I recreated the query using a new tactic and it's working great.