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!

Register Log in
  • 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

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
 
Sort by date Sort by votes
It might be best to upload an Excel file with records from each table in a separate tab/sheet. Make sure you have a sheet that contains the exact output you would like.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
Yes, my point was to start with the 'inner' Select, see if returns expected result, even if ID was hard-coded. And then go from there.


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Andy,
I finally some time to address this...

I tried your idea of using a hard coded agentID.

This query returns correct data, but I get 10 records instead of 5? If it would return 5 I will be able to do it from there...

Code: 
Select TOP 5 Applications.ApplicationYear, License.* 
FROM Applications , License
Where Applications.MasterID=License.ID 
Where Applications.ApplicationStatus='PENDING' 
And Applications.ApplicationYear>='2018' 
And License.AssignedAgent='AMK' 
Order By Applications.ApplicationYear


Lyndon
 
Upvote 0 Downvote
That does not look right:

Code: 
Select TOP 5 Applications.ApplicationYear, License.* 
FROM Applications , License[red]
Where[/red] Applications.MasterID=License.ID [red]
Where[/red] Applications.ApplicationStatus='PENDING' 
And Applications.ApplicationYear>='2018' 
And License.AssignedAgent='AMK' 
Order By Applications.ApplicationYear

Re-typing doesn't help anybody, always do Copy/Paste.


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
>What file formats can I upload?

Pretty much anything you like ...
 
Upvote 0 Downvote
Code: 
Select TOP 5 Applications.ApplicationYear, License.* FROM Applications, License Where Applications.MasterID=License.ID And Applications.ApplicationStatus='PENDING' And Applications.ApplicationYear>='2018' And License.AssignedAgent='AMK' Order By Applications.ApplicationYear

Lyndon
 
Upvote 0 Downvote
Don't you just need [tt]TOP 5 Applications.ApplicationYear[/tt] since this will be a part of a larger SQL:
[tt]...
WHERE ApplicationYear IN
( Select TOP 5 Applications.ApplicationYear
From...
[/tt]
I would expect the output of your inner query to be something like:[tt]
ApplicationYear
2010
2013
2014
2015
2017[/tt]

Maybe the [tt], License.* [/tt] messes up your output?


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Some of us can't download accdbs from the web. Did you also include the desired output in the file?

Consider zipping the file and trying again or maybe someone can download.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
Man this was hard for me to get.

But I solved it using my server side code by looping over the set of agents and using the union keyword. The dynamic query it creates runs fast and give me the desired result.

I never figured out how to do it in pure sql.
Code: 
Select Top 5 Applications.ApplicationYear, License.First+','+Left(License.LastName,1) As FirstName, License.id, License.AssignedAgent 
From Applications, License 
Where Applications.MasterID=License.ID 
And Applications.ApplicationStatus='Pending' 
And Applications.ApplicationYear >= '2018' 
And AssignedAgent='AMA' 
And License.ID IN (35,305,2373,3677,3798,3896,3959,3965,4086,5901,6039,6113,6153,6453,6967,7301,7319,7489,7490,8133,8134,8439,8590,8654,8672,8756,8757) 
Union 
Select Top 5 Applications.ApplicationYear, License.First+','+Left(License.LastName,1) As FirstName, License.id, License.AssignedAgent 
From Applications, License 
Where Applications.MasterID=License.ID 
And Applications.ApplicationStatus='Pending' 
And Applications.ApplicationYear >= '2018' 
And AssignedAgent='KEV' 
And License.ID IN (35,305,2373,3677,3798,3896,3959,3965,4086,5901,6039,6113,6153,6453,6967,7301,7319,7489,7490,8133,8134,8439,8590,8654,8672,8756,8757) 
Union 
Select Top 5 Applications.ApplicationYear, License.First+','+Left(License.LastName,1) As FirstName, License.id, License.AssignedAgent 
From Applications, License 
Where Applications.MasterID=License.ID 
And Applications.ApplicationStatus='Pending' 
And Applications.ApplicationYear >= '2018' 
And AssignedAgent='LAR' 
And License.ID IN (35,305,2373,3677,3798,3896,3959,3965,4086,5901,6039,6113,6153,6453,6967,7301,7319,7489,7490,8133,8134,8439,8590,8654,8672,8756,8757) 
Union
Select Top 5 Applications.ApplicationYear, License.First+','+Left(License.LastName,1) As FirstName, License.id, License.AssignedAgent 
From Applications, License 
Where Applications.MasterID=License.ID 
And Applications.ApplicationStatus='Pending' 
And Applications.ApplicationYear >= '2018' 
And AssignedAgent='GAR' 
And License.ID IN (35,305,2373,3677,3798,3896,3959,3965,4086,5901,6039,6113,6153,6453,6967,7301,7319,7489,7490,8133,8134,8439,8590,8654,8672,8756,8757) 
Union
Select Top 5 Applications.ApplicationYear, License.First+','+Left(License.LastName,1) As FirstName, License.id, License.AssignedAgent 
From Applications, License 
Where Applications.MasterID=License.ID 
And Applications.ApplicationStatus='Pending' 
And Applications.ApplicationYear >= '2018' 
And AssignedAgent='PET' 
And License.ID IN (35,305,2373,3677,3798,3896,3959,3965,4086,5901,6039,6113,6153,6453,6967,7301,7319,7489,7490,8133,8134,8439,8590,8654,8672,8756,8757) 
Order By AssignedAgent, ApplicationYear, LastName, FirstName


Output:
YEAR 	AGENT 	FIRST,LAST
2018 	AMA 	LORI,B
2019 	AMA 	MICHAEL,D
2019 	AMA 	GRANT,J
2019 	AMA 	RICHARD,J
2019 	AMA 	JAMES,M
2018 	KEV 	KEVIN,N
2019 	LAR 	JEFFREY,A
2019 	LAR 	JASON,C
2019 	LAR 	ELLIOTT,M
2019 	LAR 	DEREK,P
2019 	PET 	LAKEISHA,H
2019 	PET 	KRISTI,W

Lyndon
 
Upvote 0 Downvote
Are you working from a different dataset to the one you gave us? I ask, because PET has no Pending status for anything in the dataset you gave us, and so shouldn't appear in the result set (nor does it have Last Name) … And if you are using a different data set, then we cannot compare like for like to see if we are getting the right results.
 
Upvote 0 Downvote
Your Select statement starts with:
[tt]
Select Top 5 Applications.ApplicationYear,
License.First+','+Left(License.LastName,1) As FirstName,
License.id, License.AssignedAgent
From Applications, License ...[/tt]

So your output should be in the format of:[pre]
ApplicationYear FirstName id AssignedAgent [/pre]

Not, as you stated:[pre]
YEAR AGENT FIRST,LAST[/pre]

All Select statements are the same (as far as I can see) except for [tt]AssignedAgent[/tt], which is a field in Applications table? License table? So instead of hard-coding AssignedAgent, you should be able to do:
[tt]
...
And Applications.ApplicationYear >= '2018'
And AssignedAgent IN ([blue]Select DISTINCT AssignedAgent From License[/blue])
And License.ID IN (35,305, ...[/tt]

And forget about UNIONs

Actually, if the 5 hard-coded Agents are all the Agents in the table, why mention them at all in the Select? Unless you have 20 Agents, and you want to have the output for certain Agents only, then the BLUE select should be set so only the 5 Agents are retrieved.

License.ID hard-coded list also should come from another Select, in my opinion.


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Code: 
[blue]SELECT StatusDate as YEAR, AssignedAgent as AGENT, FirstName As [FIRST, LAST]
FROM 
(
    SELECT License.AssignedAgent, License.id, License.FirstName, License.StatusDate
    FROM License INNER JOIN Applications ON License.id = Applications.MasterID
    WHERE License.Status="Pending" AND Applications.ApplicationStatus="Pending"
    GROUP BY License.AssignedAgent, License.id, License.FirstName, License.StatusDate
) AS MS1
WHERE ID IN 
(
    SELECT TOP 5 ID
    FROM 
    (
        SELECT License.AssignedAgent, License.id, License.FirstName, License.StatusDate
        FROM License INNER JOIN Applications ON License.id = Applications.MasterID
        WHERE License.Status="Pending" AND Applications.ApplicationStatus="Pending"
        GROUP BY License.AssignedAgent, License.id, License.FirstName, License.StatusDate
    ) AS MS2
   WHERE MS2.AssignedAgent =MS1.AssignedAgent AND MS2.StatusDate >=MS1.StatusDate
)[/blue]
 
Upvote 0 Downvote
I'm getting this error from my server side ODBC driver. Never seen it before...

Error Executing Database Query.
Syntax error at token 0, line 0 offset 0.

And the attached message when using Access Run Query
Lyndon
 
 https://files.engineering.com/getfile.aspx?folder=bb5e18fb-4b27-43d6-b0ca-5214b64f0e38&file=accessError.JPG
Upvote 0 Downvote
>attached message

Well … yes, you seem to not have copied and pasted accurately. There should be a closing parenthesis after [tt]>=MS1.StatusDate[/tt]

>Error Executing Database Query.
>Syntax error at token 0, line 0 offset 0.

You'll get that if the conststructed SQL string is ever actually empty or null.
 
Upvote 0 Downvote
Here is my clipboard content. Same error.

Code: 
SELECT StatusDate as YEAR, AssignedAgent as AGENT, FirstName As [FIRST, LAST]
FROM 
(
    SELECT License.AssignedAgent, License.id, License.FirstName, License.StatusDate
    FROM License INNER JOIN Applications ON License.id = Applications.MasterID
    WHERE License.Status="Pending" AND Applications.ApplicationStatus="Pending"
    GROUP BY License.AssignedAgent, License.id, License.FirstName, License.StatusDate
) AS MS1
WHERE ID IN 
(
    SELECT TOP 5 ID
    FROM 
    (
        SELECT License.AssignedAgent, License.id, License.FirstName, License.StatusDate
        FROM License INNER JOIN Applications ON License.id = Applications.MasterID
        WHERE License.Status="Pending" AND Applications.ApplicationStatus="Pending"
        GROUP BY License.AssignedAgent, License.id, License.FirstName, License.StatusDate
    ) AS MS2
   WHERE MS2.AssignedAgent =MS1.AssignedAgent AND MS2.StatusDate >=MS1.StatusDate
)

Lyndon
 
Upvote 0 Downvote
Can't help you there then. That code, which comes directly from my copy of Access, works 100% fine here against the tables that you sent on 6th Dec. The error message certainly seems to think the bracket is missing. And I can generate the EXACT same error message if I delete that final bracket.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

datajockey123
  • Locked
  • Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
270
dhookom
dhookom
btamsgn
  • Locked
  • Question
how to create a crosstab query 1
Replies
1
Views
413
dhookom
dhookom
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
418
combo
combo
Moss100
  • Locked
  • Question
Append query dropping leading zero 2
Replies
4
Views
352
Andrzejek
Andrzejek
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
374
TheresAlwaysAWay
TheresAlwaysAWay

Part and Inventory Search

Sponsor

Back
Top