Returning Last Team based on Dates (1 Viewer)

[Kylep]

I'm running into a bear of a problem... My table contains lots of survey data, and looks simular to:

Table: tblData
Agent_ID     Agent_Name     Group_Name   TimeStamp    Avg_Score
12345         Kyle          Team3         1/1/05 8AM    2
12345         Kyle          Team3         1/1/05 9AM    3
12345         Kyle          Team2         1/1/05 10AM   2
12345         Kyle          Team2         1/1/05 11AM   4
12345         Kyle          Team2         1/1/05 12PM   1
12345         Kyle          Team2         1/1/05 1PM     2

My query is set up to Group by Agent_ID, take the last Agent_name, the last Group_Name, count the Timestamp and average the Avg_Score.. The result looks like this

Query: qrySummary
Agent_ID     Agent_Name     Group_Name   Count    Avg_Score
12345        Kyle           Team3        6           2.33

I had assumed that using the last Group_Name would use the order they appear in the table to judge what is last, but it appears it chooses the last one in alphabetical order. I need the Group_Name to show what group the person was in for the last Survey they recieved.

I did accomplish this by making a query to take the last Timestamp for each Agent_ID, then another query linking that to tblData based on the Timestamp (which is unique) to get the Group_Name and Agent_Name for each Agent_ID, then bringing that all together in my qrySummary which has the Count and Avg score. (there is actually a little more to this that complicates it, but this is the gist)

So, my problem is that by doing this change, the Query runs much slower then before. I've been trying to figure out how to get this into one query, but for the life of me i can't get it figured out. I'm also hoping there is just a plain easier way that i'm missing.

All help is appreciated!

(edited to get my tables to look better)

 

[EMP]

You don't need the third query "qrySummary".

Just do the Count and Avg score in two additional columns in the first query. Then you can display these two fields in the second query too.


In the following thread, Jon K showed how you can achieve it in just one query with a correlated subquery. He also pointed out the limitations of the one-query approach:-
http://www.access-programmers.co.uk/forums/showthread.php?t=82160

In Access, a join is generally more efficient than a correlated subquery.

 

[Kylep]

For the life of me I couldn't get the syntax on the SQL to work, but I did try your idea of putting the counts and averages in the first query, to cut out the middle query. It worked and sped it up quite a bit, enough to make it quite usable again.

Thanks a lot for the tip!