Chart using calculated field

[BarryC]

How do I show results from calculated fields on a chart. I have a report with
two calculated fields, one is a date and the other is a sum. Is there a way I
can express those fields on a chart. Thanks.

 

[Duane Hookom]

Generally speaking, you must be able to get your values into the Chart's Row
Source query.

 

[BarryC]

How can I do that, I've been trying with no sucess.

 

[Duane Hookom]

I don't know how you could do that since you haven't told us anything about
your data or your calculations.

 

[BarryC]

I have two calculated fields on a report. Both of the fields are in a sorted
footer section. The fields display a calculated month and a calculated sum,
i.e. Jan. 553. I would like to show the calculations in a chart for all
months. Thanks.

Thanks.

 

[Duane Hookom]

I can only suggest again that you must be able to create your values in a
select or totals query.

 

[Tim Colbourn]

Hi Duane,

Does this mean that you can't create charts from the calculated fields in a
report?
I have tried to use a query as the row source for the chart, but have a
problem in that I can't replicate the calculations I make in the report in
the Totals query (used as the row source) as it won't let me use the Sqr()
function in the query (although it works in the report).
Any suggestions apart from using Excel? (I already have the analysis in
Excel but have to copy and paste all the data into Excel manually every time
as the 'Analyse it with Excel' doesn't copy the calculated fields in the
report; therefore I want the chart to be automated in Access)

Cheers,
Tim

 

[Duane Hookom]

Sqr() is available in Access queries.
A chart has a Row Source which is usually a query, table, or SQL statement.
If you can't calculate a value in the Row Source, it generally can't be
displayed in the chart. There are some aggregated values that can be added
such as trend lines.

If you can't calculate a value in a query, you have a couple options. I
think the simplest is to create a small temporary table with the desired
values and use the table in the chart Row Source.

 

[Tim Colbourn]

Thanks Duane,
You are right that the Sqr() does work in Queries, it just doesn't work when
I want to get the Square root of another expression in the query as that
expression wants me to enter data before displaying the query results (even
though it displays when I don't enter anything in the parameter box)
Anyway, I have found some code linked from another post that exports data
from queries to specific cells of and Excel spreadsheet so am using this
instead as Excel is far easier to do analyses and make nice graphs in! (and I
already have an Excel that I use for analysing the data)
Cheers,
Tim