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 |
#11
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
I was wondering if I can bother the masters one more time about this?
If it at all possible to make this part... (Between 200801 and 201012)... a user input with supplying a month and then the year instead of year and month, (i.e. 6-09, 10-10, etc.)? I only ask because I'm being asked and the four digit year preceeding the month has confused a few ppl. Thanks Again! Rob "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 |
#12
|
|||
|
|||
Need help with SQL Statement in MS Access 2003 Chart
There a couple of ways. You can use prompts for dates or use an unbound form
to enter dates for the criteria. Here is how to use prompts -- WHERE (((tblMonthList.MyMonth) Between Format(CVDate([Enter start date]),"yyyymm") And Format(CVDate([Enter date date]),"yyyymm") )) And of course use a date format that Access will recognize. -- Build a little, test a little. "Rob" wrote: I was wondering if I can bother the masters one more time about this? If it at all possible to make this part... (Between 200801 and 201012)... a user input with supplying a month and then the year instead of year and month, (i.e. 6-09, 10-10, etc.)? I only ask because I'm being asked and the four digit year preceeding the month has confused a few ppl. Thanks Again! Rob "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 | |
|
|