Need Top Ten Records From Four Different Groups

[JHopman]

I am working on an accident database for a four county area. I need to find the top 10 accident locations for each county for a report. I am currently using a query with DISTINCTROW to group identical streets in each county and find out both the number of accidents, and the total number of injuries or dead.

SELECT DISTINCTROW [tblAccidentData].[DE_COUNTYNAMETXT] AS County, [tblAccidentData].[DE_RDWYNAMETXT] AS Location, [tblAccidentData].[DE_INTERNAMETXT] AS Intersection, (Sum([tblAccidentData].[DE_INJUREDNMB])+Sum([tblAccidentData].[DE_DEADNMB])) AS [Injury / Fatality], Count(*) AS [Total Accidents]
FROM tblAccidentData
WHERE ((([tblAccidentData].[DE_RDWYNAMETXT])<>"") And (([tblAccidentData].[DE_INTERNAMETXT])<>"") And (Year([tblAccidentData].[DE_COLLDTE])=[Accident Year]))
GROUP BY [tblAccidentData].[DE_COUNTYNAMETXT], [tblAccidentData].[DE_RDWYNAMETXT], [tblAccidentData].[DE_INTERNAMETXT]
ORDER BY [tblAccidentData].[DE_COUNTYNAMETXT], Count(*) DESC , [tblAccidentData].[DE_RDWYNAMETXT], [tblAccidentData].[DE_INTERNAMETXT];

I found that I could use the "TOP 10" command in my query to limit my results to the first ten records returned, but then I only end up with 10 records for the first county. I should end up with 40 records (10 for each county). I think I need to limit the print out using the Report, but I don't know how. Thanks in advance for your help.

 

[HBWAL]

How about using 4 different queries (10 for each) and 1 union query that brings them all together. Then using this as your datasource.

 

[Lightning]

Have a look at this thread

Thread181-852291

HTH
Lightning