A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need help with SQL Statement in MS Access 2003 Chart



 
 
Thread Tools Display Modes
  #11  
Old May 13th, 2010, 06:20 PM posted to microsoft.public.access.reports
Rob
external usenet poster
 
Posts: 1,029
Default Need help with SQL Statement in MS Access 2003 Chart

I was wondering if I can bother the masters one more time about this?

If it at all possible to make this part... (Between 200801 and 201012)... a
user input with supplying a month and then the year instead of year and
month, (i.e. 6-09, 10-10, etc.)? I only ask because I'm being asked and the
four digit year preceeding the month has confused a few ppl.

Thanks Again!
Rob



"KARL DEWEY" wrote:

Ok, you got to build a list of month in a table. Best would be YYYYMM as the
data.
tblMonthList --
MyMonth - Number (201012, 201011, 201010, 201009, ...
DisplayMon - Text (Dec, Nov, Oct, Sep, ...

SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"yyyymm")=
tblMonthList.MyMonth, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date],
"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Has Due Date],
Sum(IIF(Format([SentOn Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty
Sent]
FROM [qryDeliveries], tblMonthList
WHERE tblMonthList.MyMonth Between 200801 and 201012
GROUP BY tblMonthList.DisplayMon
ORDER BY tblMonthList.MyMonth;


--
Build a little, test a little.


"Rob" wrote:

That would be my error then... The grouping was set automatically by the
Access Chart Wizard. As of yet I have no idea on how to change that. What I
am hoping to acheive is a total count of all received per month, all sent per
month, and all that are marked as being due per month. However there is not
a one for one relationship. Some receives, (short for Request Received), do
not have a due date and not all have been sent out yet.

What I did was create a query that pulled everything for this year and then
used to Chart to try to graphically show the data in Access. I also exported
the query to Excel and used formula to get the data for an Excel chart.
That's how I know what I want to see as a result but so far I cannot get
Access to do what Excel can.


"KARL DEWEY" wrote:

Maybe I am not understanding what you are trying to count in each case. What
I posted shows count of the date fields that had dates with the same month as
that of the [Received Date] as that is how you are grouping.

--
Build a little, test a little.


"Rob" wrote:

Wow, Very close... The error level is no where nearly as bad as what I was
getting but there's still something not quite right. For instance, the
numbers for april and may are reporting the same as originally but Jan-Mar
has a +/- of 1 error.

Does it matter that I have blanks in the data?



"KARL DEWEY" wrote:

I left off closing parenthesis for the Sum. Try it now --
SELECT (Format([Received Date],"MMM")), Count([Received Date]) AS [Qty
Received], Sum(IIF(Month([DueBy Date])=Month([Received Date]), 1, 0)) AS [Has
Due Date], Sum(IIF(Month([SentOn Date])=Month([Received Date]), 1, 0)) AS
[Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));

--
Build a little, test a little.


"Rob" wrote:

Thank You for helping... I get a syntax error that says "Missing Operator"
in query expression and then a huge string of the SQL statement that starts
and stops at the below.

Sum(IIF(Month([DueBy Date])=Month([Received Date]), 1, 0) AS [Has Due Date],
Sum(IIF(Month([SentOn Date])=Month([Received Date]), 1, 0) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1)

"KARL DEWEY" wrote:

Try this --
SELECT (Format([Received Date],"MMM")),
Count([Received Date]) AS [Qty Received],
Sum(IIF(Month([DueBy Date])=Month([Received Date]), 1, 0) AS [Has Due Date],
Sum(IIF(Month([SentOn Date])=Month([Received Date]), 1, 0) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));

--
Build a little, test a little.


"Rob" wrote:

My Access Chart is not showing the data correctly. It's only counting line
items by month per row and I am trying to get it to count each column as it's
own data set.

Chart SQL:
SELECT (Format([Received Date],"MMM")),
Count([Received Date]) AS [Qty Received],
Count([DueBy Date]) AS [Has Due Date],
Count([SentOn Date]) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));


Should Reflect:
RCVD Has Due Sent
Jan 16 11 8
Feb 11 10 9
Mar 23 19 16
Apr 12 13 6
May 1 8 1
Jun
Jul
Aug
Sep
Oct
Nov
Dec



Actually Reflects:
RCVD Due Sent
Jan 16 16 15
Feb 11 9 8
Mar 23 23 17
Apr 12 12 0
May 1 1 0
Jun
Jul
Aug
Sep
Oct
Nov
Dec


I'm certainly doing something wrong and I have no idea how to fix it.

Thanks In Advance,
Rob

  #12  
Old May 14th, 2010, 12:02 AM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Need help with SQL Statement in MS Access 2003 Chart

There a couple of ways. You can use prompts for dates or use an unbound form
to enter dates for the criteria.

Here is how to use prompts --

WHERE (((tblMonthList.MyMonth) Between Format(CVDate([Enter start
date]),"yyyymm") And Format(CVDate([Enter date date]),"yyyymm") ))

And of course use a date format that Access will recognize.

--
Build a little, test a little.


"Rob" wrote:

I was wondering if I can bother the masters one more time about this?

If it at all possible to make this part... (Between 200801 and 201012)... a
user input with supplying a month and then the year instead of year and
month, (i.e. 6-09, 10-10, etc.)? I only ask because I'm being asked and the
four digit year preceeding the month has confused a few ppl.

Thanks Again!
Rob



"KARL DEWEY" wrote:

Ok, you got to build a list of month in a table. Best would be YYYYMM as the
data.
tblMonthList --
MyMonth - Number (201012, 201011, 201010, 201009, ...
DisplayMon - Text (Dec, Nov, Oct, Sep, ...

SELECT tblMonthList.DisplayMon, Sum(IIF(Format([Received Date],"yyyymm")=
tblMonthList.MyMonth, 1, 0)) AS [Qty Received], Sum(IIF(Format([DueBy Date],
"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Has Due Date],
Sum(IIF(Format([SentOn Date],"yyyymm")= tblMonthList.MyMonth, 1, 0)) AS [Qty
Sent]
FROM [qryDeliveries], tblMonthList
WHERE tblMonthList.MyMonth Between 200801 and 201012
GROUP BY tblMonthList.DisplayMon
ORDER BY tblMonthList.MyMonth;


--
Build a little, test a little.


"Rob" wrote:

That would be my error then... The grouping was set automatically by the
Access Chart Wizard. As of yet I have no idea on how to change that. What I
am hoping to acheive is a total count of all received per month, all sent per
month, and all that are marked as being due per month. However there is not
a one for one relationship. Some receives, (short for Request Received), do
not have a due date and not all have been sent out yet.

What I did was create a query that pulled everything for this year and then
used to Chart to try to graphically show the data in Access. I also exported
the query to Excel and used formula to get the data for an Excel chart.
That's how I know what I want to see as a result but so far I cannot get
Access to do what Excel can.


"KARL DEWEY" wrote:

Maybe I am not understanding what you are trying to count in each case. What
I posted shows count of the date fields that had dates with the same month as
that of the [Received Date] as that is how you are grouping.

--
Build a little, test a little.


"Rob" wrote:

Wow, Very close... The error level is no where nearly as bad as what I was
getting but there's still something not quite right. For instance, the
numbers for april and may are reporting the same as originally but Jan-Mar
has a +/- of 1 error.

Does it matter that I have blanks in the data?



"KARL DEWEY" wrote:

I left off closing parenthesis for the Sum. Try it now --
SELECT (Format([Received Date],"MMM")), Count([Received Date]) AS [Qty
Received], Sum(IIF(Month([DueBy Date])=Month([Received Date]), 1, 0)) AS [Has
Due Date], Sum(IIF(Month([SentOn Date])=Month([Received Date]), 1, 0)) AS
[Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));

--
Build a little, test a little.


"Rob" wrote:

Thank You for helping... I get a syntax error that says "Missing Operator"
in query expression and then a huge string of the SQL statement that starts
and stops at the below.

Sum(IIF(Month([DueBy Date])=Month([Received Date]), 1, 0) AS [Has Due Date],
Sum(IIF(Month([SentOn Date])=Month([Received Date]), 1, 0) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1)

"KARL DEWEY" wrote:

Try this --
SELECT (Format([Received Date],"MMM")),
Count([Received Date]) AS [Qty Received],
Sum(IIF(Month([DueBy Date])=Month([Received Date]), 1, 0) AS [Has Due Date],
Sum(IIF(Month([SentOn Date])=Month([Received Date]), 1, 0) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));

--
Build a little, test a little.


"Rob" wrote:

My Access Chart is not showing the data correctly. It's only counting line
items by month per row and I am trying to get it to count each column as it's
own data set.

Chart SQL:
SELECT (Format([Received Date],"MMM")),
Count([Received Date]) AS [Qty Received],
Count([DueBy Date]) AS [Has Due Date],
Count([SentOn Date]) AS [Qty Sent]
FROM [qryDeliveries]
GROUP BY (Year([Received Date])*12 + Month([Received
Date])-1),(Format([Received Date],"MMM"));


Should Reflect:
RCVD Has Due Sent
Jan 16 11 8
Feb 11 10 9
Mar 23 19 16
Apr 12 13 6
May 1 8 1
Jun
Jul
Aug
Sep
Oct
Nov
Dec



Actually Reflects:
RCVD Due Sent
Jan 16 16 15
Feb 11 9 8
Mar 23 23 17
Apr 12 12 0
May 1 1 0
Jun
Jul
Aug
Sep
Oct
Nov
Dec


I'm certainly doing something wrong and I have no idea how to fix it.

Thanks In Advance,
Rob

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.