Need help with SQL Statement in MS Access 2003 Chart
Maybe I am not understanding what you are trying to count in each case. What
I posted shows count of the date fields that had dates with the same month as
that of the [Received Date] as that is how you are grouping.
--
Build a little, test a little.
"Rob" wrote:
Wow, Very close... The error level is no where nearly as bad as what I was
getting but there's still something not quite right. For instance, the
numbers for april and may are reporting the same as originally but Jan-Mar
has a +/- of 1 error.
Does it matter that I have blanks in the data?
"KARL DEWEY" wrote:
I left off closing parenthesis for the Sum. Try it now --
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:
Thank You for helping... I get a syntax error that says "Missing Operator"
in query expression and then a huge string of the SQL statement that starts
and stops at the below.
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)
"KARL DEWEY" wrote:
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
|