Chart Wizard - Getting Duplicate Charts (Access 2007) 2

[jrozier]

I'm not sure if this should be in the Form or the Query section but I'll start here since you have to invoke the chart wizard from a form.

I have created a chart based on data from a crosstab query. The chart looks and works perfectly, however the data behind the chart creates 8 records that supply sales record for each person.

For example, here is the backend data that powers the chart:

Person Date Sales
Person1 1/2009 20
Person1 2/2009 25
Person1 3/2009 22
Person1 4/2009 18
Person1 5/2009 25
Person1 6/2009 14
Person1 7/2009 30
Person1 8/2009 24
Person2 1/2009 14
Person2 2/2009 21
etc...

So, for Person 1, I have a chart that shows Person1's sales for the 8 month time frame and it works great. However, since my chart is powered by this table, I get 7 more IDENTICAL charts before it moves on to Person2.

Is there anyway I can only show ONE chart per Person?

 

[kjv1611]

I've not tried using charts in Access to date, have only done in Excel primarily, so take my advice with a grain of salt.

I think I know the answer, though.

You said:

However, since my chart is powered by this table, I get 7 more IDENTICAL charts before it moves on to Person2.

That is your answer (I think). Create a query which only pulls the necessary record of your data set, and then set the control source of the chart to that query, rather than the underlying table.

So, your query could be something like this (maybe):

SELECT * FROM MyTable WHERE Person = '" & Forms!MyFormName!txtPerson & "'"

I think I may be a big off with the single/double quotes - not 100% off hand, b/c don't have the time to test.. but here's how I'd go about it:

1. Build a query in design view.
2. Select your table for the source
3. Select whatever fields you want to return (you'll need to select all of them individually if you want to select all, b/c it'll be required for entering criteria).
4. Under the Person field, go to the Criteria box, and then click on the build button on your toolbar (or I believe you can also right-click, then build within the box).
5. In that window, select the form, and Person control, or whatever control will pick which person you're reporting on... make sure that gets entered in the builder window..
6. Then OK out of that window.
7. Your build window will probalby look something like:

=Forms!frmMyFormName.txtPersonName

Of course the table/form/field/control names in the examples here are not necessarily going to be what you used.

Try all that out, and post back letting us know if it worked... and/or if you have any questions..

--

"If to err is human, then I must be some kind of human!" -Me

 

[jrozier]

I may have been unclear in what I really need. I actually need a chart for each person, not just one. I know how to only show one person like you described above.

I can take the data above and convert that data into a crosstab query to get something like this:

Person 1/2009 2/2009 etc
Person1 20 32
Person2 29 42
etc

This actually works perfectly and gives me the "data" view I need, but when I try to build a chart from this data, the actual sales data (which would be the sumofsales in the crosstab query)isn't available to add to the chart wizard. Also, the chart wizard will only let you define up to 6 fields in a chart. In this case, i'll have about 8 fields (for the 8 months i'm tracking sales data).

Summing this up, I need to be able to see a line graph of all sales from the past 8 months from all salesman (currently over 100 salesman)using Microsoft Charts in Access 2007. Like I said in the original ticket, I can actually getting this information now, but i'm getting the same chart duplicated 7 times before it moves on to person2.

 

[dhookom]

What section of the form is the chart in? Is it in a detail section that contains 7 records?

Duane
Hook'D on Access
MS Access MVP

 

[jrozier]

I tried the chart in the detail section with and without a break on "person" and i tried the same in the form footer. They all give me the same result. 8 charts per salesman for the 8 months of sales.

 

[dhookom]

Is your chart actually in a form or is it in a report?

Duane
Hook'D on Access
MS Access MVP

 

[jrozier]

It's in a form.

 

[dhookom]

A form header is one section and gets rendered once. How do you get multiple copies of the chart? Are you suggesting multiple lines or multiple charts?

What is the Row Source of your chart?

Duane
Hook'D on Access
MS Access MVP

 

[jrozier]

I'm getting multiple charts because the row source of my chart is this:

Person Date Sales
Person1 1/2009 20
Person1 2/2009 25
Person1 3/2009 22
Person1 4/2009 18
Person1 5/2009 25
Person1 6/2009 14
Person1 7/2009 30
Person1 8/2009 24
Person2 1/2009 14
Person2 2/2009 21


So for person1, I get a line chart with a graph of all of their sales figures, but i get that same chart 7 times because there are a total of 8 records for person1. When i get to record 9, it starts with Person2 and it shows the same graph for him until record 17, and it goes on and on. The line graph I get is perfect, but I just need one chart per salesman.

 

[SkipVought]

Are you going to VIEW ALL the charts at once?

If not you only need ONE chart and a Combobox control to select the person.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I agree with Skip. I don't understand why your form would have the same (or similar) record source as the chart's row source.

Duane
Hook'D on Access
MS Access MVP

 

[jrozier]

Are you going to VIEW ALL the charts at once?

If not you only need ONE chart and a Combobox control to select the person.

I agree with Skip. I don't understand why your form would have the same (or similar) record source as the chart's row source.

Thank you for your continued assistance. I have been working on this for 2 weeks and i'm about to pull my hair out.

We actually need the ability to view and print one chart for each associate. So, in a case where we have 100 salesman, I need one chart for each salesman showing their respective sales for the past 8 months, which would give me 100 copies. If I do this now, i'll get 800 copies.

Even if I use the combo box to choose one person, I would still get 8 total charts for the one person. Using this technique to generate 100 charts wouldn't be feasible because of the time it will take to lookup 100 salesman and click print.

 

[SkipVought]

Using this technique to generate 100 charts wouldn't be feasible because of the time it will take to lookup 100 salesman and click print.

Frankly, if I had this task, I would not sit there and lookup 100 salesmen to print one-by-one either. That's why you write a PROGRAM to do that!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jrozier]

Ok. I can use VBA to do that. But I still have the issue of multiple charts.

 

[SkipVought]

I would still get 8 total charts for the one person

You could create 8 different charts, and then loop thru the 100 names. Then for each name loop thru the 8 charts.

I might even opt for modifying ONE CHART eight times for each person programatically. I have done something similar before.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jrozier]

Ok... that may work.

Lemme work on that and see what i come up with. Thanks!

 

[SkipVought]

Of course it WILL work. Coding is often a tradeoff between the effort needed to code and the effort the coding might save over time: Sort of an ROI evaluation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

We actually need the ability to view and print one chart for each associate.

You shouldn't print forms. Use a report bound to a record source of each unique associate (one record per associate). Add your chart to the report and set the Link Master/Child properties to the associate field. There is absolutely no coding involved in doing this.

Duane
Hook'D on Access
MS Access MVP

 

[jrozier]

You shouldn't print forms. Use a report bound to a record source of each unique associate (one record per associate). Add your chart to the report and set the Link Master/Child properties to the associate field. There is absolutely no coding involved in doing this.

That's it.

I read online somewhere that the chart wizard in Access2007 was moved to the Forms section only. I never attempted to create the charts in Reports. I made a grouping level based on Person, created the chart there with a page break, and gave me exactly what i wanted. One line graph per salesman on one page.

Thanks everyone for your help!