Group my month and then total each location - query

  • Thread starter Thread starter Erik
  • Start date Start date
E

Erik

Arr_ICAO = specific location
Arr_Date = concatenated date and time (not included in the code below)

Every mission number has it's own specific location accompanied by a
date...

What I have below is almost giving me what I want. Say that possible
locations are, "a,b,c, and d"... The below querie tells me that
location "a" has a total count of 34. Location "b" has a total count
of 16, and so on... This is the entire total throughout the database
though, I need to split this up by how many times location "a" (along
with the rest of the Arr_ICAO) appears per month.

--------------

SELECT Count(tbl_Missions.Mission_Number) AS CountOfMission_Number,
tbl_Missions.Arr_ICAO
FROM tbl_Missions
GROUP BY tbl_Missions.Arr_ICAO
ORDER BY tbl_Missions.Arr_ICAO;
 
SELECT Format(Arr_Date, "yyyy-mm") as YearMonth
, tbl_Missions.Arr_ICAO
, Count(tbl_Missions.Mission_Number) AS CountOfMission_Number
FROM tbl_Missions
GROUP BY tbl_Missions.Arr_ICAO, Format(Arr_Date, "yyyy-mm")
ORDER BY tbl_Missions.Arr_ICAO;

Or you can use the year and month functions instead.

SELECT Year(Arr_Date) as TheYear
, Month(Arr_Date) as TheMonth
, tbl_Missions.Arr_ICAO
, Count(tbl_Missions.Mission_Number) AS CountOfMission_Number
FROM tbl_Missions
GROUP BY Year(Arr_Date)
, Month(Arr_Date)
, tbl_Missions.Arr_ICAO
ORDER BY tbl_Missions.Arr_ICAO;




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Using the year and month functions as you have illustrated is exactly
what I needed. Thank you so much for your help!

Erik
 
Back
Top