Getting last 10 records 1

[StarhawkGamer]

I have two tables, 1 has a list of agents, the other is quality control surveys for all agents. is there a way in Access to get the last 10 QAs for each agent in a single query?

 

[r937]

SELECT a.agentname
     , s.surveyname
     , s.surveydate
  FROM agents AS a
INNER
  JOIN surveys AS s
    ON s.agentID = a.ID
   AND ( SELECT COUNT(*) 
           FROM surveys  
          WHERE agentID = s.agentID
            AND surveydate > s.surveydate ) < 10

r937.com | rudy.ca

 

[StarhawkGamer]

How would you change it if you were just doing it with the single table and getting the agent's ID to cross-reference in Excel?

 

[r937]

huh?

i would import whatever i have in excel into a database table



r937.com | rudy.ca

 

[StarhawkGamer]

I guess I'm just not understanding the second select statement in the query. Could you explain the logic behind the line

AND ( SELECT COUNT(*) 
           FROM surveys  
          WHERE agentID = s.agentID
            AND surveydate > s.surveydate ) < 10

 

[r937]

okay, here's what that subquery does

it counts the number of surveys for the same agent that have a later date than this survey

if that count is less than 10, then this survey has to be among the latest 10 for this agent

which is what you wanted, right?

"get the last 10 QAs for each agent in a single query"

r937.com | rudy.ca

 

[StarhawkGamer]

Ok, attempted to set it up and I keep getting a syntax error on the subquery.

SELECT Agents.[Advocate ID], QA.[SessionID], QA.[FATAL IR]
FROM Agents
INNER JOIN QA
ON QA.[UNIT ID] = Agents.[Advocate ID]
AND ( SELECT COUNT(*)
FROM QA
WHERE [UNIT ID] = QA.[UNIT ID]
AND [Survey Date] QA.[Survey Date]) < 10;

 

[r937]

um, yeah, i think that's msaccess, being petulant again

the ON clause may need parentheses

oh, and you must use an alias for QA in the outer query, the way i did

  FROM Agents
INNER 
  JOIN QA [green]AS foo[/green]
    ON [red]([/red]
       [green]foo[/green].[UNIT ID] = Agents.[Advocate ID]
   AND ( SELECT COUNT(*)
           FROM QA
          WHERE [UNIT ID] = [green]foo[/green].[UNIT ID]
            AND [Survey Date] > QA.[Survey Date] ) < 10
       [red])[/red]

r937.com | rudy.ca

 

[StarhawkGamer]

tried those changes and still get a syntax error. here is the altered code.

SELECT Agents.[Advocate ID], foo.[SessionID], foo.[FATAL IR]
FROM Agents
INNER JOIN QA as foo
ON (
foo.[UNIT ID] = Agents.[Advocate ID]
AND ( SELECT COUNT(*)
FROM QA
WHERE [UNIT ID] = foo.[UNIT ID]
AND [SURVEY DATE] > qa.[SURVEY DATE]) < 10);

I'm using MSAccess 2007, if that makes any difference.

 

[r937]

aarghh, i forgot one foo

change QA. to foo. in the last line



r937.com | rudy.ca

 

[StarhawkGamer]

changed...no effect.

 

[r937]

crikey!! let's see what happens now...

SELECT Agents.[Advocate ID]
     , foo.[SessionID]
     , foo.[FATAL IR]
  FROM Agents
     , QA as foo
 WHERE foo.[UNIT ID] = Agents.[Advocate ID]
   AND ( SELECT COUNT(*)
           FROM QA
          WHERE [UNIT ID] = foo.[UNIT ID]
            AND [SURVEY DATE] > foo.[SURVEY DATE]) < 10

r937.com | rudy.ca

 

[StarhawkGamer]

Thank you. It works perfectly!!! Made one change from doing it by date to doing it by survey ID# (because multiple surveys can be done on a single day)