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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Charts in Reports
I would like to have a report that just contains a column chart to
graphically show the count of the types of complaints and the timeframe . I have built a simple query, with a 'Date of Complaint' and 'Complaint Category' field. The query has a date parameter prompt of 'BeginDate' and 'EndDate'. I have used the Chart Wizard when trying to create a chart, but it doesn't graph anything, the graph shows up blank. Can anyone help me make a simple graph based on my query.I am new to graphs in Access and would appreciate any help. Thanks in advance..... |
#2
|
|||
|
|||
Charts in Reports
The chart will display the values from the chart control's Row Source
property. The records may be filtered by values entered into the Link Master/Child properties or any criteria in the query. We would need to know at least the Row Source SQL view as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I would like to have a report that just contains a column chart to graphically show the count of the types of complaints and the timeframe . I have built a simple query, with a 'Date of Complaint' and 'Complaint Category' field. The query has a date parameter prompt of 'BeginDate' and 'EndDate'. I have used the Chart Wizard when trying to create a chart, but it doesn't graph anything, the graph shows up blank. Can anyone help me make a simple graph based on my query.I am new to graphs in Access and would appreciate any help. Thanks in advance..... |
#3
|
|||
|
|||
Charts in Reports
The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT
(Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY (Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date of Complaint],"MMM 'YY")) PIVOT [Category Description]; The Link Child/Query criteria is blank. There is a parameter in the query which asks for a begin and end date. If I take the date parameter out, then the graph works, but when I use the parameter, then I get the following error messages: 1) The Microsoft Jet database engine does not recognize [Start Date] as a valid field name. 2) An error occurred while sending data to the OLE server. I would like the date prompt included since I only want to be able to graph a certain date range. Thanks..... "Duane Hookom" wrote: The chart will display the values from the chart control's Row Source property. The records may be filtered by values entered into the Link Master/Child properties or any criteria in the query. We would need to know at least the Row Source SQL view as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I would like to have a report that just contains a column chart to graphically show the count of the types of complaints and the timeframe . I have built a simple query, with a 'Date of Complaint' and 'Complaint Category' field. The query has a date parameter prompt of 'BeginDate' and 'EndDate'. I have used the Chart Wizard when trying to create a chart, but it doesn't graph anything, the graph shows up blank. Can anyone help me make a simple graph based on my query.I am new to graphs in Access and would appreciate any help. Thanks in advance..... |
#4
|
|||
|
|||
Charts in Reports
IMHO, parameter prompts are never appropriate user interface. Use controls on
forms for all user input. Crosstab queries require the explicit declaration of the data type of all parameters. If you leave the parameter prompts then go to your query design and select Query - Parameters and enter something like: [Start Date] DateTime [End Date] DateTime You can also enter all possible Category Description values into the Column Headings property of the crosstab query. BTW: did I suggest you replace parameter prompts with controls on forms ;-) -- Duane Hookom Microsoft Access MVP "Amber" wrote: The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT (Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY (Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date of Complaint],"MMM 'YY")) PIVOT [Category Description]; The Link Child/Query criteria is blank. There is a parameter in the query which asks for a begin and end date. If I take the date parameter out, then the graph works, but when I use the parameter, then I get the following error messages: 1) The Microsoft Jet database engine does not recognize [Start Date] as a valid field name. 2) An error occurred while sending data to the OLE server. I would like the date prompt included since I only want to be able to graph a certain date range. Thanks..... "Duane Hookom" wrote: The chart will display the values from the chart control's Row Source property. The records may be filtered by values entered into the Link Master/Child properties or any criteria in the query. We would need to know at least the Row Source SQL view as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I would like to have a report that just contains a column chart to graphically show the count of the types of complaints and the timeframe . I have built a simple query, with a 'Date of Complaint' and 'Complaint Category' field. The query has a date parameter prompt of 'BeginDate' and 'EndDate'. I have used the Chart Wizard when trying to create a chart, but it doesn't graph anything, the graph shows up blank. Can anyone help me make a simple graph based on my query.I am new to graphs in Access and would appreciate any help. Thanks in advance..... |
#5
|
|||
|
|||
Charts in Reports
I have created an unbound form named 'Paramform' with two text boxes,
StartDate and EndDate. I have my report with just the graph in it. My question is, what should my Report Record Source and Graph Row Source be? I've tried changing these sources to different things, such as Between forms!Paramform!StartDate and forms!ParamForm!EndDate. I still get error messages when I run this. To make the form I used the following directions: Use a Form to enter the parameters. Create an unbound form. Add 2 unbound text controls. Set their format to a valid date format. Name them "StartDate" and "EndDate" Add a Command Button to the form. Code the button's click event: Me.Visible = False Name this form 'ParamForm'. As criteria in the query date field write: Between forms!Paramform!StartDate and forms!ParamForm!EndDate Use the same criteria in the Graph record source. Next, code the report's Open event: DoCmd.OpenForm "ParamForm", , , , , acDialog Code the report's Close event: DoCmd.Close acForm, "ParamForm" "Duane Hookom" wrote: IMHO, parameter prompts are never appropriate user interface. Use controls on forms for all user input. Crosstab queries require the explicit declaration of the data type of all parameters. If you leave the parameter prompts then go to your query design and select Query - Parameters and enter something like: [Start Date] DateTime [End Date] DateTime You can also enter all possible Category Description values into the Column Headings property of the crosstab query. BTW: did I suggest you replace parameter prompts with controls on forms ;-) -- Duane Hookom Microsoft Access MVP "Amber" wrote: The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT (Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY (Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date of Complaint],"MMM 'YY")) PIVOT [Category Description]; The Link Child/Query criteria is blank. There is a parameter in the query which asks for a begin and end date. If I take the date parameter out, then the graph works, but when I use the parameter, then I get the following error messages: 1) The Microsoft Jet database engine does not recognize [Start Date] as a valid field name. 2) An error occurred while sending data to the OLE server. I would like the date prompt included since I only want to be able to graph a certain date range. Thanks..... "Duane Hookom" wrote: The chart will display the values from the chart control's Row Source property. The records may be filtered by values entered into the Link Master/Child properties or any criteria in the query. We would need to know at least the Row Source SQL view as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I would like to have a report that just contains a column chart to graphically show the count of the types of complaints and the timeframe . I have built a simple query, with a 'Date of Complaint' and 'Complaint Category' field. The query has a date parameter prompt of 'BeginDate' and 'EndDate'. I have used the Chart Wizard when trying to create a chart, but it doesn't graph anything, the graph shows up blank. Can anyone help me make a simple graph based on my query.I am new to graphs in Access and would appreciate any help. Thanks in advance..... |
#6
|
|||
|
|||
Charts in Reports
Try this as the Chart's Row Source. Make sure the form is open and dates are
entered. PARAMETERS forms!Paramform!StartDate DateTime, forms!ParamForm!EndDate DateTime; TRANSFORM Count(*) AS [Count] SELECT Format([Date of Complaint],"MMM 'YY") FROM [qryGRAPH] WHERE [Date Of Complaint] Between forms!Paramform!StartDate and forms!ParamForm!EndDate GROUP BY Year([Date of Complaint])*12 + Month([Date of Complaint])-1,Format([Date of Complaint],"MMM 'YY") PIVOT [Category Description]; Since your report "just contains a column chart" then your report shouldn't have any record source. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I have created an unbound form named 'Paramform' with two text boxes, StartDate and EndDate. I have my report with just the graph in it. My question is, what should my Report Record Source and Graph Row Source be? I've tried changing these sources to different things, such as Between forms!Paramform!StartDate and forms!ParamForm!EndDate. I still get error messages when I run this. To make the form I used the following directions: Use a Form to enter the parameters. Create an unbound form. Add 2 unbound text controls. Set their format to a valid date format. Name them "StartDate" and "EndDate" Add a Command Button to the form. Code the button's click event: Me.Visible = False Name this form 'ParamForm'. As criteria in the query date field write: Between forms!Paramform!StartDate and forms!ParamForm!EndDate Use the same criteria in the Graph record source. Next, code the report's Open event: DoCmd.OpenForm "ParamForm", , , , , acDialog Code the report's Close event: DoCmd.Close acForm, "ParamForm" "Duane Hookom" wrote: IMHO, parameter prompts are never appropriate user interface. Use controls on forms for all user input. Crosstab queries require the explicit declaration of the data type of all parameters. If you leave the parameter prompts then go to your query design and select Query - Parameters and enter something like: [Start Date] DateTime [End Date] DateTime You can also enter all possible Category Description values into the Column Headings property of the crosstab query. BTW: did I suggest you replace parameter prompts with controls on forms ;-) -- Duane Hookom Microsoft Access MVP "Amber" wrote: The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT (Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY (Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date of Complaint],"MMM 'YY")) PIVOT [Category Description]; The Link Child/Query criteria is blank. There is a parameter in the query which asks for a begin and end date. If I take the date parameter out, then the graph works, but when I use the parameter, then I get the following error messages: 1) The Microsoft Jet database engine does not recognize [Start Date] as a valid field name. 2) An error occurred while sending data to the OLE server. I would like the date prompt included since I only want to be able to graph a certain date range. Thanks..... "Duane Hookom" wrote: The chart will display the values from the chart control's Row Source property. The records may be filtered by values entered into the Link Master/Child properties or any criteria in the query. We would need to know at least the Row Source SQL view as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I would like to have a report that just contains a column chart to graphically show the count of the types of complaints and the timeframe . I have built a simple query, with a 'Date of Complaint' and 'Complaint Category' field. The query has a date parameter prompt of 'BeginDate' and 'EndDate'. I have used the Chart Wizard when trying to create a chart, but it doesn't graph anything, the graph shows up blank. Can anyone help me make a simple graph based on my query.I am new to graphs in Access and would appreciate any help. Thanks in advance..... |
#7
|
|||
|
|||
Charts in Reports
This worked perfectly......thanks for your help!
"Duane Hookom" wrote: Try this as the Chart's Row Source. Make sure the form is open and dates are entered. PARAMETERS forms!Paramform!StartDate DateTime, forms!ParamForm!EndDate DateTime; TRANSFORM Count(*) AS [Count] SELECT Format([Date of Complaint],"MMM 'YY") FROM [qryGRAPH] WHERE [Date Of Complaint] Between forms!Paramform!StartDate and forms!ParamForm!EndDate GROUP BY Year([Date of Complaint])*12 + Month([Date of Complaint])-1,Format([Date of Complaint],"MMM 'YY") PIVOT [Category Description]; Since your report "just contains a column chart" then your report shouldn't have any record source. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I have created an unbound form named 'Paramform' with two text boxes, StartDate and EndDate. I have my report with just the graph in it. My question is, what should my Report Record Source and Graph Row Source be? I've tried changing these sources to different things, such as Between forms!Paramform!StartDate and forms!ParamForm!EndDate. I still get error messages when I run this. To make the form I used the following directions: Use a Form to enter the parameters. Create an unbound form. Add 2 unbound text controls. Set their format to a valid date format. Name them "StartDate" and "EndDate" Add a Command Button to the form. Code the button's click event: Me.Visible = False Name this form 'ParamForm'. As criteria in the query date field write: Between forms!Paramform!StartDate and forms!ParamForm!EndDate Use the same criteria in the Graph record source. Next, code the report's Open event: DoCmd.OpenForm "ParamForm", , , , , acDialog Code the report's Close event: DoCmd.Close acForm, "ParamForm" "Duane Hookom" wrote: IMHO, parameter prompts are never appropriate user interface. Use controls on forms for all user input. Crosstab queries require the explicit declaration of the data type of all parameters. If you leave the parameter prompts then go to your query design and select Query - Parameters and enter something like: [Start Date] DateTime [End Date] DateTime You can also enter all possible Category Description values into the Column Headings property of the crosstab query. BTW: did I suggest you replace parameter prompts with controls on forms ;-) -- Duane Hookom Microsoft Access MVP "Amber" wrote: The chart control's Row source = TRANSFORM Count(*) AS [Count] SELECT (Format([Date of Complaint],"MMM 'YY")) FROM [qryGRAPH] GROUP BY (Year([Date of Complaint])*12 + Month([Date of Complaint])-1),(Format([Date of Complaint],"MMM 'YY")) PIVOT [Category Description]; The Link Child/Query criteria is blank. There is a parameter in the query which asks for a begin and end date. If I take the date parameter out, then the graph works, but when I use the parameter, then I get the following error messages: 1) The Microsoft Jet database engine does not recognize [Start Date] as a valid field name. 2) An error occurred while sending data to the OLE server. I would like the date prompt included since I only want to be able to graph a certain date range. Thanks..... "Duane Hookom" wrote: The chart will display the values from the chart control's Row Source property. The records may be filtered by values entered into the Link Master/Child properties or any criteria in the query. We would need to know at least the Row Source SQL view as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Amber" wrote: I would like to have a report that just contains a column chart to graphically show the count of the types of complaints and the timeframe . I have built a simple query, with a 'Date of Complaint' and 'Complaint Category' field. The query has a date parameter prompt of 'BeginDate' and 'EndDate'. I have used the Chart Wizard when trying to create a chart, but it doesn't graph anything, the graph shows up blank. Can anyone help me make a simple graph based on my query.I am new to graphs in Access and would appreciate any help. Thanks in advance..... |
Thread Tools | |
Display Modes | |
|
|