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