0

How do I find the month with the most crime in every year and the number only of the crimes in that month of that year using SQL in Microsoft Access?

I tried this, hoping it would give me the result I wanted.

SELECT COUNT(*) AS CRIME_COUNT, CRIME_MONTH, CRIME_YEAR
FROM CRIMEDATA
GROUP BY CRIME_YEAR, [DISTINCT CRIME_MONTH], CRIME_MONTH
ORDER BY COUNT(*) DESC

It prompted me to enter a parameter value for [distinct crime_month], so I just entered 1, and I got a table with the rows ordered by descending crime counts and the corresponding months and years, but it showed every month in every year. I get the same result not using [distinct crime_month]. How can I get the result I'm looking for?

jarlh
  • 42,561
  • 8
  • 45
  • 63
KATIE.E
  • 1
  • Build a query that aggregates crime count by month/year then use that query as source for a TOP 1 month per year. Review https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access – June7 Aug 08 '23 at 16:25
  • More about TOP N http://allenbrowne.com/subquery-01.html#TopN – June7 Aug 08 '23 at 18:03

1 Answers1

2

Consider:

Build aggregate query and use that query as source for TOP N.

Query1

SELECT COUNT(*) AS CRIME_COUNT, CRIME_MONTH, CRIME_YEAR
FROM CRIMEDATA
GROUP BY CRIME_YEAR, CRIME_MONTH;

Query2

SELECT Query1.* FROM Query1 WHERE CRIME_MONTH IN(
    SELECT TOP 1 CRIME_MONTH FROM Query1 AS Dupe 
    WHERE Dupe.CRIME_YEAR = Query1.CRIME_YEAR 
    ORDER BY Dupe.CRIME_COUNT DESC)
ORDER BY CRIME_YEAR, CRIME_MONTH;

Or this alternative for Query2

SELECT Query1.CRIME_YEAR, Query1.CRIME_MONTH, Query1.CRIME_COUNT
FROM Query1 INNER JOIN (
    SELECT Max(CRIME_COUNT) AS MCC, CRIME_YEAR FROM Query1 
    GROUP BY CRIME_YEAR) AS Q2
ON Query1.CRIME_YEAR = Q2.CRIME_YEAR AND Query1.CRIME_COUNT = Q2.MCC;

Be aware neither will eliminate tied data. Months with same highest count in a year will retrieve.

June7
  • 19,874
  • 8
  • 24
  • 34