|
| 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
May 12th, 2010, 04:14 PM
posted to microsoft.public.access.reports
|
|||
|
|||
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
May 12th, 2010, 04:28 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
May 12th, 2010, 04:49 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
May 12th, 2010, 05:42 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
May 12th, 2010, 06:16 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
May 12th, 2010, 06:29 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Hybrid Mode
