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

 

[dhookom]

Is ApplicationYear a text or numeric field? If numeric (which it should be) remove the quotes. Also in Access the single quotes should typically be double-quotes.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

Try

FROM License L INNER JOIN Applications A on L.ID = A.MasterID

 

[dhookom]

Strongm,
Good catch. The original posts were in the SQL Server forum which supports JOIN.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

Sorry, I had one of those days lost to meetings!

The "INNER" term stopped the error; Thanks strongm! However, I'm not getting the row count I expected. So I changed the row count to 5. I know I have 5 assigned agents so I expected to get 25 rows back.

I'm getting 384 rows. If I correctly explained my requirement, the query below should return no more than 25 rows.

Let me try express my requirement another time. For each of the assigned agents I want, no more than, their 5 oldest pending cases. This is for a side bar menu that provides a quick link to the most urgent work.

A previous post mentioned that the ApplicationYear column should be numeric. The ApplicationYear is a text column only populated dynamically, no user errors are likely. Prior to 2018 this query would be illogical as some of these data elements were first introduced to the DB.

It seems to me we are not grouping to 5 rows per agent yet. But, I don't know how to proceede...

Again, all this help is very much appreciated!

SELECT License.*, Applications.ApplicationYear
FROM License, Applications
WHERE Applications.ApplicationYear>='2018'
AND License.Status='PENDING'
AND License.ID=[Applications].[MasterID] 
And License.ID In 
(
SELECT TOP 5 License.id
			FROM License L INNER 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

 

[Andrzejek]

their 5 oldest pending cases" - I would concentrate first on getting the [tt]TOP 5 IDs[/tt] for one (hard-coded) Agent in the 'inner' Select statement, something like:
[tt]
SELECT TOP 5 L.id
FROM License L INNER JOIN Applications A on L.ID = A.MasterID
WHERE L.AssignedAgent = [blue]1234[/blue]
Order By A.ApplicationYear
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I expect you have some duplicate data someplace that you are noticing. I would try add something unique to the subquery like:

SELECT License.*, Applications.ApplicationYear
FROM License, Applications
WHERE Applications.ApplicationYear>='2018'
AND License.Status='PENDING'
AND License.ID=[Applications].[MasterID] 
And License.ID In 
(
SELECT TOP 5 L.id
			FROM License L INNER JOIN Applications A on L.ID = A.MasterID
			WHERE L.AssignedAgent = License.AssignedAgent
 			Order By A.ApplicationYear, L.LastName, L.FirstName, L.ID 
	)
ORDER BY License.AssignedAgent, Applications.ApplicationYear, License.LastName, License.FirstName;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

The group of assigned agents would be dynamic. Today I have 6 agents with a pending record, yesterday it was 5.

If an agent has no pending work, they are not on the list.

Lyndon

 

[dhookom]

What are you seeing that is wrong with your results? Is License.ID unique? What are the relationships of your tables?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

Today I should have only gotten 30 records. At 8 AM I was getting all 384 currently pending records. Where I need only the five oldest records per agent.

Application Table (is a child table, new record for each customer Status Change statusSet{Pending, Approved, Denied...})
MasterID (is populated by License.ID on insert)
ApplicationYear (varChar(4) year populated on insert from a customer Form)
Status (We need each agents 5 oldest Status='Pending' records.)

License Table (is the parent table, one row for each customer for life)
Name, ID, LicNumber, Contact_info, Most Recent [Status]... (customer_id is the table auto populate column License.ID)

License->ID = Applications->MasterID
(Licensene) = (Applications:Many)

So, we need to query for each agents five oldest License.Status='PENDING' records. Then, using server side code, i'll use the query build a url link to take them to the edit page for that record.

We have a side bar menu that a whole department has for their home page in the app. On that menu we need to see 5 of each agents links, if they have that many. If they have zero they shouldn't show up at all.

If they have less than five pending apps, leadership will see quickly and reallocate resources...
So it runs all day long and changes quickly so it needs to be as efficient as possible. Plan is to get it to work with five url's.
Then i'll design a management variable interface that the users will use to change the quantity of url's they desire to see personally.






Lyndon

 

[dhookom]

Try add filtering into the subquery:

SELECT License.*, Applications.ApplicationYear
FROM License, Applications
WHERE Applications.ApplicationYear>="2018"
AND License.Status="PENDING"
AND License.ID=[Applications].[MasterID] 
And License.ID In 
(
SELECT TOP 5 L.id
			FROM License L INNER JOIN Applications A on L.ID = A.MasterID
			WHERE L.AssignedAgent = License.AssignedAgent
			AND A.ApplicationYear>="2018" AND L.Status="PENDING"
 			Order By A.ApplicationYear, L.LastName, L.FirstName, L.ID 
	)
ORDER BY License.AssignedAgent, Applications.ApplicationYear, License.LastName, License.FirstName;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

Try something like

[blue]SELECT *
FROM (SELECT DISTINCT License.*, Applications.ApplicationYear
FROM License INNER JOIN Applications ON License.ID = Applications.MasterID) AS m
WHERE ApplicationYear IN
(
SELECT TOP 5 ApplicationYear
FROM (SELECT DISTINCT License.*, Applications.ApplicationYear
FROM License INNER JOIN Applications ON License.ID = Applications.MasterID)
WHERE AssignedAgent=m.AssignedAgent
ORDER BY ApplicationYear
)
ORDER BY AssignedAgent, ApplicationYear, LastName, FirstName;[/blue]

 

[Andrzejek]

Lyndon , did you try my suggestion?


---- Andy

There is a great need for a sarcasm font.

 

[LyndonOHRC]

Andy
I saw what you wrote, but did not know how to make the term "WHERE L.AssignedAgent = 1234" Dynamic as I don't know what agents may have eligible records. I tried use my server side code to construct a dynamic union query, but it ran too slow.



Lyndon

 

[strongm]

>I don't know what agents may have eligible records.

Don't think Andy is suggesting that you do. He is trying to get you to see if the query works as expected for 1 test agent. I wouldn't expect that it will, however ...

(just to clarify - if you run Andy's code stand alone, it will seem to work - but it won't return the results you want when used in the full query, mainly because of the reliance on Licence ID, with its one to many relationship with Applications table)

 

[LyndonOHRC]

strongm

I'm saying that's why I don't understand how to proceed.

In way over my head probably...

Lyndon

 

[strongm]

Have you tried my query to see if it gets any closer to the results that you want?

 

[dhookom]

LyndonOHRC,

Can you provide some sample data for the two tables and the exact results you expect to return from the query?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

strongm

this one?

It seemed to lock up like an infinite loop of something. So I never got any output.

SELECT *
FROM (SELECT DISTINCT License.*, Applications.ApplicationYear
FROM License INNER JOIN Applications ON License.ID = Applications.MasterID) AS m
WHERE ApplicationYear IN
(
SELECT TOP 5 ApplicationYear
FROM (SELECT DISTINCT License.*, Applications.ApplicationYear
FROM License INNER JOIN Applications ON License.ID = Applications.MasterID)
WHERE AssignedAgent=m.AssignedAgent
ORDER BY ApplicationYear
)
ORDER BY AssignedAgent, ApplicationYear, LastName, FirstName;

Lyndon

 

[LyndonOHRC]

Duane
I'll put a data package together... What file formats can I upload?

Lyndon