Recent content by LyndonOHRC

Too much personal privacy info to just post tables. Yes, I made sure the queries match. Lyndon

Well thank you for trying! Lyndon

Here is my clipboard content. Same error. 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...

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...

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. Select Top 5...

Here is a data set Lyndonhttps://files.engineering.com/getfile.aspx?folder=7d1ba082-fc5d-45b2-a9a0-855e06711ebb&file=agentQueryTest.accdb

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

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... Select TOP 5 Applications.ApplicationYear, License.* FROM...

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

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...

strongm I'm saying that's why I don't understand how to proceed. In way over my head probably... Lyndon

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

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...

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

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...