Need help with SQL Statement in MS Access 2003 Chart

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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
 
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"));
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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)

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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)

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?



:

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.


:

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)

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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?



:

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.


:

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)

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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.


:

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?



:

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.


:

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)

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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?



:

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.


:

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)

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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.


:

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?



:

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.


:

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)

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top