Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Help with a grouping query

Status
Not open for further replies.

LyndonOHRC

Programmer
Sep 8, 2005
603
US
Please see
[link ]Thread Incorrectly Created in SQL Forum[/url] for start of thread.

Still getting syntax error in query
Code:
Select License.*, Applications.ApplicationYear
	From License, Applications
	Where  License.ID=Applications.MasterID
	And License.Status='PENDING'
	And Applications.ApplicationYear >= '2018'
	And License.id IN 
	(
		SELECT TOP 50 License.id
			FROM License L JOIN Applications A on L.ID = A.MasterID
			WHERE L.AssignedAgent = License.AssignedAgent
 			Order By A.ApplicationYear, L.LastName, L.FirstName
	)
	Order By License.AssignedAgent, Applications.ApplicationYear, License.LastName, License.FirstName


Lyndon
 
Just to check:you are 100% sure the code pasted into Access matches what is in the clipboard. Not guessing, that you have actually checked charecter by charecter

In fact can you post the Access dB where it isn't working, and is producing the error message that you detail?
 
Too much personal privacy info to just post tables.

Yes, I made sure the queries match.

Lyndon
 
I believe this is what you want or at least very close.

SQL:
SELECT License.*, License.AssignedAgent, Applications.ApplicationDate, Applications.TableID
FROM Applications INNER JOIN License ON Applications.MasterID = License.id
WHERE (((Applications.TableID) In 
(SELECT TOP 5 TableID
 FROM Applications A INNER JOIN License L ON A.MasterID = L.ID
 WHERE L.AssignedAgent = [License].[AssignedAgent] AND L.Status ="Pending" ORDER BY A.ApplicationDate, A.TableID)))
ORDER BY License.AssignedAgent, Applications.ApplicationDate;

In the data you sent, there were no Pending for KEV and PET

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Yep - that should work as well (although I think you need to add an [tt]AND a.ApplicationStatus="Pending" [/tt] before the ORDER BY clause

Oddly seems to be fractionally slower, on my PC at least, than my version (but since the OP does not seem able to get my version working, that's moot)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top