Need help with SQL Statement in MS Access 2003 Chart
Try this --
SELECT (Format([Received Date],"MMM")),
Count([Received Date]) AS [Qty Received],
Sum(IIF(Month([DueBy Date])=Month([Received Date]), 1, 0) AS [Has Due Date],
Sum(IIF(Month([SentOn Date])=Month([Received Date]), 1, 0) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));
--
Build a little, test a little.
"Rob" wrote:
My Access Chart is not showing the data correctly. It's only counting line
items by month per row and I am trying to get it to count each column as it's
own data set.
Chart SQL:
SELECT (Format([Received Date],"MMM")),
Count([Received Date]) AS [Qty Received],
Count([DueBy Date]) AS [Has Due Date],
Count([SentOn Date]) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));
Should Reflect:
RCVD Has Due Sent
Jan 16 11 8
Feb 11 10 9
Mar 23 19 16
Apr 12 13 6
May 1 8 1
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Actually Reflects:
RCVD Due Sent
Jan 16 16 15
Feb 11 9 8
Mar 23 23 17
Apr 12 12 0
May 1 1 0
Jun
Jul
Aug
Sep
Oct
Nov
Dec
I'm certainly doing something wrong and I have no idea how to fix it.
Thanks In Advance,
Rob
|