Add target to embedded chart

[honeypot3]

Hi there

I have an embedded chart which is linked with a field Contractor ID on a main report so that each time the contractor ID changes on each page a new graph is drawn of their data - works fab. But I need to be able to set a line on the each chart with the same target values so a comparison can be easily made. Is there a way of doing this without just drawing a line on the chart with the drawing tools or having repeating targets throughout the query driving the chart?

 

[dhookom]

Are the targets different for each contractor?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[honeypot3]

Hi Duane

No they are the same set of 8 figures for all contractors

Sandra

 

[dhookom]

It isn't clear why you have a "set of 8 figures". Can you describe the row source of your chart? Also, where and how are you storing the "set of 8 figures"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[honeypot3]

Hi Duane

Well the 8 figures are targets set for Key Performance Indicators for all contractors. As yet they are not stored anywhere as I do not knoe how to manipulate the data such that these numbers can be pulled through without a lot of repeating information. At the moment the row source of the cahrt is some SQL that Access created after the chart was inserted as below:

PARAMETERS [forms]![dateselect]![month] Text ( 255 ), [forms]![dateselect]![year] Text ( 255 ), [forms]![dateselect]![tomonth] Text ( 255 ), [forms]![dateselect]![toyear] Text ( 255 ); TRANSFORM Sum(ContractQuestLinkAvg2.AvgOfResp) AS SumOfAvgOfResp SELECT ContractQuestLinkAvg2.[Question Text] FROM ContractQuestLinkAvg2 GROUP BY ContractQuestLinkAvg2.QuesID, ContractQuestLinkAvg2.[Question Text] ORDER BY ContractQuestLinkAvg2.QuesID PIVOT Format([CalcDate],"yy-mm(mmm-yy)");

Basically the user chooses a from and to date in a form which then pulls averages of scores from an underlying query to graph scores per month per question asked. This sits on a report which lists each contractor. The graph is linked to the contractor ID.

 

[dhookom]

Can you create a single record table (tblFigures) with eight fields for figures (Figure1 - Figure8).
Add the one record with the eight values and change your Row Source to:

PARAMETERS [forms]![dateselect]![month] Text ( 255 ),
[forms]![dateselect]![year] Text ( 255 ),
[forms]![dateselect]![tomonth] Text ( 255 ),
[forms]![dateselect]![toyear] Text ( 255 );
TRANSFORM Sum(AvgOfResp) AS SumOfAvgOfResp
SELECT [Question Text], Figure1, Figure2, Figure3,
Figure4, Figure5, Figure6, Figure7, Figure8
FROM ContractQuestLinkAvg2, tblFigures
GROUP BY QuesID, [Question Text], Figure1, Figure2,
Figure3, Figure4, Figure5, Figure6, Figure7, Figure8
ORDER BY ContractQuestLinkAvg2.QuesID
PIVOT Format([CalcDate],"yy-mm(mmm-yy)");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[honeypot3]

Hi Duane

This sort of worked in that a line appeared for each field in the tblfigures table but that meant I had multiple target lines when I only wanted one line showing the 8 different values. So I thought that if I made tblfigures just one field with 8 records that this would solve the problem. I am now only getting one target line but the same questiontext being repeated along the x axis 4 times each!!

This is how the SQL looks now:

PARAMETERS [forms]![dateselect]![month] Text ( 255 ), [forms]![dateselect]![year] Text ( 255 ), [forms]![dateselect]![tomonth] Text ( 255 ), [forms]![dateselect]![toyear] Text ( 255 );
TRANSFORM Sum(ContractQuestLinkAvg2.AvgOfResp) AS SumOfAvgOfResp
SELECT ContractQuestLinkAvg2.[Question Text], tblFigures.Figure1
FROM ContractQuestLinkAvg2, tblFigures
GROUP BY ContractQuestLinkAvg2.QuesID, ContractQuestLinkAvg2.[Question Text], tblFigures.Figure1
ORDER BY ContractQuestLinkAvg2.QuesID
PIVOT Format([CalcDate],"yy-mm(mmm-yy)");

Any ideas on how just to get the one target line drawing the values 9,5,8,8,10,8,10,10 without the repeated questions on the X axis?

Sandra

 

[dhookom]

Are the 8 values each linked to a question?

I am at a loss as to what the 8 values have to do with anything else in your crosstab.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]