Max per month

Experts, how would I be able to show a MAX for [Available WC]+[Available OD] AS AvailCumWCOD but for the month?  The field the keeps the date is [date].  

SELECT Import_FC_Archive.*, [Available WC]+[Available OD] AS AvailCumWCOD
FROM Import_FC_Archive
WHERE (((Import_FC_Archive.Date) Between DateSerial(Year(Date()),Month(Date())-3,1) And DateSerial(Year(Date()),Month(Date())+4,0)));


thank you
pdvsaProject financeAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
What do you think of with "but for the month"?

If for any month, then:
SELECT 
    Format(Import_FC_Archive.Date, "yyyy-mm") AS YearMonth,
    Max([Available WC]+[Available OD]) AS AvailCumWCOD
FROM 
    Import_FC_Archive
GROUP BY 
    Format(Import_FC_Archive.Date, "yyyy-mm")

Open in new window

/gustav
0
 
John TsioumprisSoftware & Systems EngineerCommented:
With out looking at a sample of your data i can give you only a generic query
SELECT Import_FC_Archive.*
	,max([Available WC] + [Available OD] AS AvailCumWCOD)
FROM Import_FC_Archive
WHERE (
		(
			(Import_FC_Archive.DATE) BETWEEN DateSerial(Year(DATE ()), Month(DATE ()) - 3, 1)
				AND DateSerial(Year(DATE ()), Month(DATE ()) + 4, 0)
			)
		)
GROUP BY [All the fields you want to group by]

Open in new window

0
 
pdvsaProject financeAuthor Commented:
thank you.
0
All Courses

From novice to tech pro — start learning today.