I am trying to count the number of items requested in year, then group them by month and plot by the type of drawing. I am pulling the information from an Access database and creating a chart in Excel by using MS Query. (Number of requests vs. Month by drawing type) This what I have so far, but when I run the query, I get the following error.
"You tried to execute a query that does not include the specified expression 'ReqCompleteddate' as part of an aggregate function."
RequestTable.ReqCompleteddate
FROM Drawingtable Drawingtable, DwgClassTable DwgClassTable, RequestTable RequestTable
WHERE Drawingtable.DWGClass = DwgClassTable.DrawingClass AND RequestTable.Itemno = Drawingtable.Itemno AND ((RequestTable.ReqCompleteddate Between {ts '2002-01-01 00:00:00'} And {ts '2002-12-31 00:00:00'}))
GROUP BY DwgClassTable.type, datepart("m", [RequestTable.ReqCompleteddate])
I also tried it this way, but I get and error "Didn't expect groupmonth after select col list."
SELECT Count(Drawingtable.Itemno), DwgClassTable.type, sum(RequestTable.ReqCompleteddate),
datepart("m",RequestTable.ReqCompleteddate) as GroupMonth
FROM Drawingtable Drawingtable, DwgClassTable DwgClassTable, RequestTable RequestTable
WHERE Drawingtable.DWGClass = DwgClassTable.DrawingClass AND RequestTable.Itemno = Drawingtable.Itemno AND ((RequestTable.ReqCompleteddate Between {ts '2002-01-01 00:00:00'} And {ts '2002-12-31 00:00:00'}))
GROUP BY DwgClassTable.type,
datepart("m", [RequestTable.ReqCompleteddate])
Any suggestions?
"You tried to execute a query that does not include the specified expression 'ReqCompleteddate' as part of an aggregate function."
RequestTable.ReqCompleteddate
FROM Drawingtable Drawingtable, DwgClassTable DwgClassTable, RequestTable RequestTable
WHERE Drawingtable.DWGClass = DwgClassTable.DrawingClass AND RequestTable.Itemno = Drawingtable.Itemno AND ((RequestTable.ReqCompleteddate Between {ts '2002-01-01 00:00:00'} And {ts '2002-12-31 00:00:00'}))
GROUP BY DwgClassTable.type, datepart("m", [RequestTable.ReqCompleteddate])
I also tried it this way, but I get and error "Didn't expect groupmonth after select col list."
SELECT Count(Drawingtable.Itemno), DwgClassTable.type, sum(RequestTable.ReqCompleteddate),
datepart("m",RequestTable.ReqCompleteddate) as GroupMonth
FROM Drawingtable Drawingtable, DwgClassTable DwgClassTable, RequestTable RequestTable
WHERE Drawingtable.DWGClass = DwgClassTable.DrawingClass AND RequestTable.Itemno = Drawingtable.Itemno AND ((RequestTable.ReqCompleteddate Between {ts '2002-01-01 00:00:00'} And {ts '2002-12-31 00:00:00'}))
GROUP BY DwgClassTable.type,
datepart("m", [RequestTable.ReqCompleteddate])
Any suggestions?