In answer to your question, I couldn't do it straight in a macro from Access, I had to convert the macro to VBA and then tweak it a bit. Here's the generic code.
Last_Month = MonthName(Month(Date))
Months_Year = Year(Date)
Name1 = "All Seasons"
Name2 = "Totals " & Last_Month & " " & Months_Year
Name3 = "Totals Season to Date"
DoCmd.TransferSpreadsheet acExport, 10, "Master_Table_Sorted_ASC_By_Season", "K:\Totals\User\History\New Imports\Totals_" & Last_Month & "_" & Months_Year & ".xlsx", False, Name1
DoCmd.Echo True, "All Seasons Transfered"
' Totals - (May 2012)
DoCmd.TransferSpreadsheet acExport, 10, "Master_MTD_Totals", "K:\Totals\User\History\New Imports\Totals_" & Last_Month & "_" & Months_Year & ".xlsx", False, Name2
DoCmd.Echo True, "MTD Totals Transfered"
' CCA Totals - Season to Date
DoCmd.TransferSpreadsheet acExport, 10, "Master_YTD_Totals", "K:\Totals\User\History\New Imports\Totals_" & Last_Month & "_" & Months_Year & ".xlsx", False, Name3
DoCmd.Echo True, "YTD Totals Transfered"
DoCmd.Echo True, "Macro Completed"
MsgBox "Done", vbInformation, "Macro Status"
Writting a query for a single season isn't a problem, I totally agree with you that doing a single season export to Excel isn't an issue. The problem for me lies with the constant adding of seasons. So far there are around a dozen seasons and several more get added each year. What I am trying to do is find a way so this doesn't come back to me every 6 or 8 weeks for a new query.
The Mater Table holds all the complied data sorted by ascending season. Ideally, it would be much easier to find away to groupby season and export the season as a group into Excel. Right now I can export to Excel as a unit or right over a dozen queries to export season independantly, but to do that would mean creating new queries as an ongoing process.