Need help grouping results.

[cljac]

I'm new to Access and need some help on how to group the results for a query. Here are the details.

tblEmployees
intDistrictCode-Pirmary Key
typEmployeeCode Primary Key
typLastName
typFirstName

tblAppsPoints
typEmployeeCode-Primary Key
intApps


intPoints

This query returns me all the districts with all the employees and their point and app totals sorted by districts accesending then by points decending.
Query
SELECT tblEmployees.intDistrictCode, tblEmployees.typEmployeeCode, tblEmployees.typLastName, tblEmployees.typFirstName, tblAppsPoints.intPoints, tblAppsPoints.intApps

FROM tblEmployees INNER JOIN tblAppsPoints ON tblEmployees.typEmployeeCode = tblAppsPoints.typEmployeeCode

ORDER BY tblEmployees.intDistrictCode, tblAppsPoints.intPoints DESC;

I need it cut down to the top 3 for each district, and when I tell access to return the top 3 with this query:
Query
SELECT TOP 3 tblEmployees.intDistrictCode, tblEmployees.typEmployeeCode, tblEmployees.typLastName, tblEmployees.typFirstName, tblAppsPoints.intPoints, tblAppsPoints.intApps

FROM tblEmployees INNER JOIN tblAppsPoints ON tblEmployees.typEmployeeCode = tblAppsPoints.typEmployeeCode

ORDER BY tblEmployees.intDistrictCode, tblAppsPoints.intPoints DESC;
I only get the top 3 for the first district in the accending order. Can anyone tell me how to get it to return the top 3 for the other districts also?

[pbaldy]

One method:

http://support.microsoft.com/kb/210039

[cljac]

I had already tried the first method on that link and that sets up a parameter query requesting the district code I want the top 3 for and only returns that 1 district.
I haven't tried the second one yet, it looked a little intimidating with a lot of places for me to make mistakes, I was hoping there was an eaiser solution. Looks like I need to give it a try though. Thanks

[pbaldy]

I use the first method in a couple of apps, and it works fine for me. Can you post the db?

[cljac]

I'm going to rebuild the database this weekend, I had an epiphany last night about Access and there are problems in my tables and relationships they way i currently have it constructed. I'll let you know how it turns out, thanks for the reply.

[pbaldy]

Gotcha. Welcome to the site by the way!

[cljac]

I got my tables straightened out, that wasn't part of the problem, I'm just not doing it right. I attached the db. I need a query that will return the top 3 agents from each district, there are 9 districts. The only thing I have been able to acheive is the top 3 from all the agents.

[pbaldy]

Yours is more complicated because the district is not in the table with the points. First create this and name it "qryPaulBase":

SELECT tblAppsPoints.txtAgentCode, tblAppsPoints.numPoints, tblAgents.numDistrictCode
FROM tblAgents INNER JOIN tblAppsPoints ON tblAgents.txtAgentCode=tblAppsPoints.txtAgentCode

Then see if this gets the correct result:

SELECT tblDMs.numDistrictCode, qryPaulBase.txtAgentCode, qryPaulBase.numPoints
FROM tblDMs INNER JOIN qryPaulBase ON tblDMs.numDistrictCode = qryPaulBase.numDistrictCode
WHERE (((qryPaulBase.txtAgentCode) In (SELECT TOP 3 txtAgentCode FROM qryPaulBase AS Alias WHERE Alias.numDistrictCode = tblDMs.numDistrictCode ORDER BY numPoints DESC)))
ORDER BY tblDMs.numDistrictCode, qryPaulBase.numPoints DESC

I'm not the strongest with SQL; there may be a simpler solution.

[cljac]

That did exactly what I needed, thank you so much. Now I just need to study that code understand what you did and why. I think I need to gain a deeper understanding of query's and what information to gather. I'm finding Access to be a facinating program but it sure has a steep learning curve. I noticed people mark their problems as solved, soon as I figure out how to do that I will.

[pbaldy]

Here you go!

https://www.accessforums.net/forum-s...lved-1828.html