need to show unfiltered data on chart

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

Hi all. I have form that contains charts. I have them currently set up
to show when they are filtered for a vendor. what I want is to show
the data when it is unfiltered. Here is the sql from the row source
for the chart.

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;

I assume it must be done through code, but i am not sure exactly.

Thanks
 
Hi all. I have form that contains charts. I have them currently set up
to show when they are filtered for a vendor. what I want is to show
the data when it is unfiltered. Here is the sql from the row source
for the chart.

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;

I assume it must be done through code, but i am not sure exactly.

Thanks
Click to expand...

Just remove the WHERE clause if you don't want it limited by vendor:

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
GROUP BY qryExc_Mesage_Weekly.Date;
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks I will try that.
 
SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Ok That works. Thanks. The other thing I was wondering is if I could
use something like this

SELECT qryExc_Mesage_Weekly_1.Date, Sum(qryExc_Mesage_Weekly_1.[Cancel
Value]) AS [Canx Value]
FROM qryExc_Mesage_Weekly_1
WHERE (((qryExc_Mesage_Weekly_1.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly_1.Date
OR
SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

This particular sql does not work but maybe you can fix it. What I
want to do is show one query when it is unfiltered nut if i filter,
show another. reason is the overall values are in the millions and the
query I use shows as say 6.5M as opposed to 6,500,000. But by vendor
it will be smaller i.e. 50,000 ro something like that. Is what I am
asking possible?
 
The only way I know to do this is to call different queries depending on the
value of the control.

If you are using the queries as the source of a report and you call the report
from the form, you can change the report's source in the call for the report.

So if you have a button that launches a report you would have code in the
button's click event like

If IsNull(Me.TxtVendorNum) Then
DoCmd.OpenReport "MyReport",acViewPreview,"QueryNameOne"
ELSE
DoCmd.OpenReport "MyReport",acViewPreview,"QueryNameTwo"
End If



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly

WHERE qryExc_Mesage_Weekly.[Vendor Name]=
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

Or if txtVendorName can have a value like "ALL" as a choice.

WHERE qryExc_Mesage_Weekly.[Vendor Name] =
[Forms]![frmExc_Mess_Charts]![txtVendorNum]
OR [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null

OR if txtVendorName can be null and Vendor name field always has a value
WHERE qryExc_Mesage_Weekly.[Vendor Name]Like
Nz([Forms]![frmExc_Mess_Charts]![txtVendorNum],"*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT qryExc_Mesage_Weekly.Date, Sum(qryExc_Mesage_Weekly.Cancel) AS
[Canx Value]
FROM qryExc_Mesage_Weekly
WHERE (((qryExc_Mesage_Weekly.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly.Date;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Ok That works. Thanks. The other thing I was wondering is if I could
use something like this

SELECT qryExc_Mesage_Weekly_1.Date, Sum(qryExc_Mesage_Weekly_1.[Cancel
Value]) AS [Canx Value]
FROM qryExc_Mesage_Weekly_1
WHERE (((qryExc_Mesage_Weekly_1.[Vendor Name])=[Forms]!
[frmExc_Mess_Charts]![txtVendorNum]))
GROUP BY qryExc_Mesage_Weekly_1.Date
OR
SELECT qryExc_Mesage_Weekly.Date
, Sum(qryExc_Mesage_Weekly.Cancel) AS [Canx Value]
FROM qryExc_Mesage_Weekly
WHERE [Forms]![frmExc_Mess_Charts]![txtVendorNum] is Null
GROUP BY qryExc_Mesage_Weekly.Date;

This particular sql does not work but maybe you can fix it. What I
want to do is show one query when it is unfiltered nut if i filter,
show another. reason is the overall values are in the millions and the
query I use shows as say 6.5M as opposed to 6,500,000. But by vendor
it will be smaller i.e. 50,000 ro something like that. Is what I am
asking possible?
Click to expand...
 
Back
Top