Need Help with a grouping query

[LyndonOHRC]

Please see
[link

http://www.tek-tips.com/viewthread.cfm?qid=1791231

]Thread Incorrectly Created in SQL Forum[/url] for start of thread.

Still getting syntax error in query

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

 

[LyndonOHRC]

Well thank you for trying!



Lyndon

 

[strongm]

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?

 

[LyndonOHRC]

Too much personal privacy info to just post tables.

Yes, I made sure the queries match.

Lyndon

 

[dhookom]

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

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

 

[strongm]

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)