Chart Help -

[grebl]

Hi Everyone,
Please forgive me – I’m a novice to Access and would like to ask for a bit of help with my charts. Any advice would be greatly appreciated.
I’ve created a customer complaint database. I need to display three different charts, each with a different query for the record source. Each query includes a parameter, which prompts the user to enter the start date and end date. Right now I’ve created three separate forms to achieve these charts. My goal is to create one form/report to display all three charts. These are the three queries:
qryCategoryAnalysis – counts the total number of complaints received for each complaint category within given date range.
qryFacilityAnalysis – counts the total number of complaints received for each manufacture facility within given date range.
qryFacilityAnalysis2 – counts the total number of complaints received per category for given manufacture facility, within given date range. This one involves a second parameter, which prompts the user to enter the name of the desired manufacture facility.
The three forms that include the separate charts are all included on the switchboard: frmCategoryAnalysis2, frmFacilityAnalysis, frmComplaintAnalysisByFacility
It seems that it should be fairly simple to combine the first two charts into one form or report. The problem is, when I do that, the parameter query pops up twice, so the user has to enter start date and end date twice each. I know there’s a way to create the prompt via a control box, rather in each separate query, but I can’t figure out how to.
Another feature I would like to add is for the chart to display the given date range at the top.
Finally, for the third chart, I’d love to display the category totals for each manufacture facility on the same chart. This would eliminate the second prompt box and make it easier for the user to compare the category totals for each manufacture facility. This is far beyond my knowledge and I don’t even know where to start. If this isn’t possible, perhaps there is a way to display the name of the given manufacture facility at the top of the chart.
Thanks in advance,
Leanne

 

[RainLover]

Here is the highlights of my suggestion.

Create a Form.

Add text Boxes to enter Dates and other parameters that you need.

In the Three Queries refer to these Text Boxes for the parameters that you require.

Test the Form and Queries.

Create Three Reports. One for each Chart using its own query.

Finally create another report which is basically blank.

Insert the other three Reports as sub reports so that when you open the main report you should see all three charts.

There will be lots of formating to do so that it looks good but patience will solve that for you.

Hope this helps.

Edit

We can address the other issues as you proceed.

 

[grebl]

Hi RainLover,

Thanks very much for your help. I've created a form, frmChartParam, and created unbound text boxes for the parameters I need: txtStartDate, txtEndDate, and txtFacility.

I referred to the date parameters in my first two queries and tested them; they both ran successfully.

Then, in the third query, I tried to include the additional parameter (txtFacility). This parameter should allow the user to enter the name of the facility. When I run this final query, no results appear.

Also, when I tested the form itself, each field read the error "#Name?." I assume this is because the unbound boxes aren't attached to any one query when opened alone. Just wanted to verify that this isn't an issue.

I've attached screen shots of the two errors I've made. Would you mind taking a look and telling me where I went wrong, before I move forward?

Thank you,
Leanne

 

[RainLover]

Also, when I tested the form itself, each field read the error "#Name?." I assume this is because the unbound boxes aren't attached to any one query when opened alone. Just wanted to verify that this isn't an issue.

It looks like the form is bound but it should not be.

Either that or the Controls have some sort of value set.

They should show as empty with no errors.

With txtFacility you could change it to a Combo Box and only select correct values.

 

[grebl]

Thanks for your response, Rain. I still can't determine what the issue is. The form's Record Source is empty, so I don't believe it's bound to anything. I'm not sure how to verify if there is any value set to the controls. Perhaps I created this input form incorrectly from the getgo. I attached screen shots of the property lists for the form and one of the control boxes..

 

[RainLover]

On the Text box you have a Control Source.

This should be blank.

Best delete the text boxes and add new ones.

The only thing you need to set is "Format"

 

[grebl]

Wonderful -- that fixed it. Thank you. I'll work on creating seperate reports for each chart, and then combining them into one report as sub-reports. I'll let you know how that goes (if you don't mind).

Thanks again for your help.

Leanne

 

[RainLover]

No worries Leanne.

Have fun.

 

[grebl]

Hello again,

So I successfully created three reports, one for each chart. These individual reports ran successfully. Then I created a blank report and added all three reports as sub-reports. When I run this report, it prompts me for the start and end date, but then only generates the first chart. The remaining charts are displayed with the generic placeholder data. Thoughts?

I didn't use the queries as the record source for the individual reports. I only used them as the source for the charts. Perhaps this is the problem?

Thank you,
Leanne

 

[RainLover]

Leanne

If each individual report with a chart runs properly and all you have done is attached these working reports to a blank report then all should be good.

Have you done this or have you made some minor change. You alluded to this in your post.

If you have done this exactly then start again with a blank report. Open it and you should see nothing.

Then add your first Sub Report. Open and Test.

Continue with all three Sub Reports.

If any of them fail look for the reason why.