Need help with SQL Statement in MS Access 2003 Chart

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
 
K

KARL DEWEY

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"));
 
R

Rob

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...
 
K

KARL DEWEY

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...
 
R

Rob

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...
 
K

KARL DEWEY

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...
 
R

Rob

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...
 
K

KARL DEWEY

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...
 
R

Rob

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...
 
R

Rob

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...
 
R

Rob

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...
 
K

KARL DEWEY

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Add" Can I display numeric zero values in crosstab query reports? 1
When building a chart Dates are been included that are not in thedate range 1
Combine 2 Charts 2007 Excel 1
Find the position of a date 6
Changing Sum Range Based on Date Chosen from Drop Down List 1
Graph changing date order 6
Graphing problem - data with multiple series 1
Add FY to Report header 3

Top