If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
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 |
#4
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
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 |
#5
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
That would be my error then... The grouping was set automatically by the
Access Chart Wizard. As of yet I have no idea on how to change that. What I am hoping to acheive is a total count of all received per month, all sent per month, and all that are marked as being due per month. However there is not a one for one relationship. Some receives, (short for Request Received), do not have a due date and not all have been sent out yet. What I did was create a query that pulled everything for this year and then used to Chart to try to graphically show the data in Access. I also exported the query to Excel and used formula to get the data for an Excel chart. That's how I know what I want to see as a result but so far I cannot get Access to do what Excel can. "KARL DEWEY" wrote: 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 |
#8
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
Ok, you got to build a list of month in a table. Best would be YYYYMM as the
data. tblMonthList -- MyMonth - Number (201012, 201011, 201010, 201009, ... DisplayMon - Text (Dec, Nov, Oct, Sep, ... SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date], "yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Has Due Date], Sum(IIF(Format([SentOn Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Sent] FROM [qryDeliveries], tblMonthList WHERE tblMonthList.MyMonth Between 200801 and 201012 GROUP BY tblMonthList.DisplayMon ORDER BY tblMonthList.MyMonth; -- Build a little, test a little. "Rob" wrote: That would be my error then... The grouping was set automatically by the Access Chart Wizard. As of yet I have no idea on how to change that. What I am hoping to acheive is a total count of all received per month, all sent per month, and all that are marked as being due per month. However there is not a one for one relationship. Some receives, (short for Request Received), do not have a due date and not all have been sent out yet. What I did was create a query that pulled everything for this year and then used to Chart to try to graphically show the data in Access. I also exported the query to Excel and used formula to get the data for an Excel chart. That's how I know what I want to see as a result but so far I cannot get Access to do what Excel can. "KARL DEWEY" wrote: 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 |
#9
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
Awesome! Thanks! However there seems to be just one thing...
SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date], "yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Has Due Date], Sum(IIF(Format([SentOn Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Sent] FROM [qryDeliveries], tblMonthList WHERE tblMonthList.MyMonth Between 201001 and 201012 GROUP BY tblMonthList.DisplayMon ORDER BY tblMonthList.MyMonth; This code gives an error of... (You tried to execute a query that does not include the specified expression 'tblMonthList.MyMonth' as part of an aggregate function) SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"mmm")= tblMonthList.DisplayMon, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date], "mmm")= tblMonthList.DisplayMon, 1, 0)) AS [Has Due Date], Sum(IIF(Format([SentOn Date],"mmm")= tblMonthList.DisplayMon, 1, 0)) AS [Qty Sent] FROM [qryDeliveries], tblMonthList WHERE tblMonthList.MyMonth Between 201001 and 201012 GROUP BY tblMonthList.DisplayMon; This Code Works But it is not Ordered by Month, it's ordered by the Alphabet and so the chart looks off/awkward. This is exciting that we're getting close to the end! "KARL DEWEY" wrote: Ok, you got to build a list of month in a table. Best would be YYYYMM as the data. tblMonthList -- MyMonth - Number (201012, 201011, 201010, 201009, ... DisplayMon - Text (Dec, Nov, Oct, Sep, ... SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date], "yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Has Due Date], Sum(IIF(Format([SentOn Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Sent] FROM [qryDeliveries], tblMonthList WHERE tblMonthList.MyMonth Between 200801 and 201012 GROUP BY tblMonthList.DisplayMon ORDER BY tblMonthList.MyMonth; -- Build a little, test a little. "Rob" wrote: That would be my error then... The grouping was set automatically by the Access Chart Wizard. As of yet I have no idea on how to change that. What I am hoping to acheive is a total count of all received per month, all sent per month, and all that are marked as being due per month. However there is not a one for one relationship. Some receives, (short for Request Received), do not have a due date and not all have been sent out yet. What I did was create a query that pulled everything for this year and then used to Chart to try to graphically show the data in Access. I also exported the query to Excel and used formula to get the data for an Excel chart. That's how I know what I want to see as a result but so far I cannot get Access to do what Excel can. "KARL DEWEY" wrote: 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 |
#10
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
I got it to work! Yiippiieeeeee! Below is what the end result is...
SELECT tblMonthList.DisplayMon, Sum(IIf(Format([Received Date],"mmm")=tblMonthList.DisplayMon,1,0)) AS [Qty Received], Sum(IIf(Format([DueBy Date],"mmm")=tblMonthList.DisplayMon,1,0)) AS [Has Due Date], Sum(IIf(Format([SentOn Date],"mmm")=tblMonthList.DisplayMon,1,0)) AS [Qty Sent] FROM qryDeliveries, tblMonthList WHERE (((tblMonthList.MyMonth) Between 201001 And 201012)) GROUP BY tblMonthList.DisplayMon, tblMonthList.MyMonth ORDER BY tblMonthList.MyMonth; Thank You VERY much for all Your Help and Time! Rob "Rob" wrote: Awesome! Thanks! However there seems to be just one thing... SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date], "yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Has Due Date], Sum(IIF(Format([SentOn Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Sent] FROM [qryDeliveries], tblMonthList WHERE tblMonthList.MyMonth Between 201001 and 201012 GROUP BY tblMonthList.DisplayMon ORDER BY tblMonthList.MyMonth; This code gives an error of... (You tried to execute a query that does not include the specified expression 'tblMonthList.MyMonth' as part of an aggregate function) SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"mmm")= tblMonthList.DisplayMon, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date], "mmm")= tblMonthList.DisplayMon, 1, 0)) AS [Has Due Date], Sum(IIF(Format([SentOn Date],"mmm")= tblMonthList.DisplayMon, 1, 0)) AS [Qty Sent] FROM [qryDeliveries], tblMonthList WHERE tblMonthList.MyMonth Between 201001 and 201012 GROUP BY tblMonthList.DisplayMon; This Code Works But it is not Ordered by Month, it's ordered by the Alphabet and so the chart looks off/awkward. This is exciting that we're getting close to the end! "KARL DEWEY" wrote: Ok, you got to build a list of month in a table. Best would be YYYYMM as the data. tblMonthList -- MyMonth - Number (201012, 201011, 201010, 201009, ... DisplayMon - Text (Dec, Nov, Oct, Sep, ... SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date], "yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Has Due Date], Sum(IIF(Format([SentOn Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty Sent] FROM [qryDeliveries], tblMonthList WHERE tblMonthList.MyMonth Between 200801 and 201012 GROUP BY tblMonthList.DisplayMon ORDER BY tblMonthList.MyMonth; -- Build a little, test a little. "Rob" wrote: That would be my error then... The grouping was set automatically by the Access Chart Wizard. As of yet I have no idea on how to change that. What I am hoping to acheive is a total count of all received per month, all sent per month, and all that are marked as being due per month. However there is not a one for one relationship. Some receives, (short for Request Received), do not have a due date and not all have been sent out yet. What I did was create a query that pulled everything for this year and then used to Chart to try to graphically show the data in Access. I also exported the query to Excel and used formula to get the data for an Excel chart. That's how I know what I want to see as a result but so far I cannot get Access to do what Excel can. "KARL DEWEY" wrote: 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 |
|
Thread Tools | |
Display Modes | |
|
|