Dawn- Create a new blank form and set its Record Source to the query that provides the data for your chart. From the field list, drag and drop text boxes onto the form for the fields that provide the data for your chart. Set the Default View to Pivot Chart and disallow other views. Switch to PivotChart view and design the chart you need. Save the form and embed it as a subform/subreport in your main report. Use the Link Master / Link Child properties to further filter your chart data if necessary.
John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Crosier, Dawn Sent: Thursday, January 05, 2006 8:55 AM To: [email protected] Subject: RE: [ms_access] Use a Form to Automate a Report (Chart) John - I created the chart using the Chart Wizard in Access 2003. It appears that it created a Pivot Chart based on the RowSource of the OLEUnbound Chart Object that it placed in my report. (The report also needs to run in Access 2002 - which it currently does) If not, could you point me in the direction to instructions on how to draw a chart using the PivotChart? Dawn Crosier Application Specialist "Education Lasts a Lifetime" -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Viescas Sent: Wednesday, January 04, 2006 9:16 PM To: [email protected] Subject: RE: [ms_access] Use a Form to Automate a Report (Chart) Dawn- What version of Access are you using? The problem is the Chart applet runs outside of Access and cannot, therefore, resolve parameters that point to objects within Access. If you have Access 2002 or later, you should be drawing your chart with a PivotChart subform embedded in your main form. Time to upgrade? The Chart applet hasn't significantly been updated seen V2 and is a piece of junk. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Crosier, Dawn Sent: Wednesday, January 04, 2006 4:55 PM To: [email protected] Subject: [ms_access] Use a Form to Automate a Report (Chart) My ultimate goal is to build a report (Chart) which is based on a query. I've got the query going. I've got the chart report going. My next step is to be able to modify the query so that I can re-use the same report for the various instances of the report. My Query: SELECT Format([Date Arrived in PCC],"mm/yyyy") AS DateArrivedInPCC, Avg(Format(WorkDays([Date Arrived in PCC],[ECO Routing Complete]),"#,###.0")) AS WorkDays FROM tblReleases GROUP BY Format([Date Arrived in PCC],"mm/yyyy"), tblReleases.[ECN Class] HAVING (((tblReleases.[ECN Class])=[Forms]![frmECNClass]![ECN Class])) ORDER BY Format([Date Arrived in PCC],"mm/yyyy"); My Report will work IF I change the query to: SELECT Format([Date Arrived in PCC],"mm/yyyy") AS DateArrivedInPCC, Avg(Format(WorkDays([Date Arrived in PCC],[ECO Routing Complete]),"#,###.0")) AS WorkDays FROM tblReleases GROUP BY Format([Date Arrived in PCC],"mm/yyyy"), tblReleases.[ECN Class] HAVING (((tblReleases.[ECN Class])="3")) ORDER BY Format([Date Arrived in PCC],"mm/yyyy"); However, if I leave the query setup the way I want it, the report provides an error: "The Microsoft Jet database engine does not recognize 'Forms!frmECNClass![ECN Class]' as a valid field name or expression." I hope I don't have to build a separate report for each of the ECN Classes. The OLEUnbound Object Frame - Microsoft Graph chart has the following as it's RowSource: TRANSFORM Avg([WorkDays]) AS [AvgOfWorkDays] SELECT [DateArrivedInPCC] FROM [qryWorkDays] GROUP BY [DateArrivedInPCC] PIVOT [WorkDays]; It seems as though I should be able to change [qryWorkDays] to a Select Statement, but I have not been able to figure out the syntax. Dawn Crosier Application Specialist "Education Lasts a Lifetime" Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
