[Cooee]

Hi Group

I've got a problem in an Access 2003 report, trying to embed a chart.
If I just wanted to chart based on a field for ALL records in a query or a table it would be easy. But it's not quite that straight-forward.

Imagine this scenario ...
I have a report which groups records into blocks, and there is a summary value in the footer of each grouped block; the detail records are hidden so only the summary footer for each block is visible. Further, let's assume that this summary value field I want to chart is a 'derived' field, like '=Max([Attempt Start Date])'; i.e. I'm only showing the highest date in the preceeding hidden block of records. This summary value isn't in the table or in the report's underlying query; it only really exists in the report and is calculated at runtime. This is pretty standard Access reporting faire.

My problem is that I can't see how to include the 'derived' summary footer value into the chart. As I wrote at the top of the post, I could easily chart a value in the report's query or the table, but I can't see how to chart a value that is calculated on the report itself.

Any clues would be gratefully received.

Thanks.

PeteB

[OBP]

Pete, why is the Max([Attempt Start Date]) only in the report.
Why not put it in the Query.
If you produce a Totals/Group Query to mimick your Report you can include the Max([Attempt Start Date]) in it and then use that for a Chart.

[Cooee]

To be honest, it's just the way I've always done it, but have never needed to have a chart before.

I don't think I'm familiar with a 'Totals/Group' query, but have had a quick read on the 'Group By' SQL clause which I think is what you mean. It looks like it just might do the trick after a bit of experimenting.

Thanks for the nudge in that direction.

PeteB

[Cooee]

That did seem to work OK in the end, although I can't seem to be able to get the chart to show the GROUPS totals, despite the chart living in the GROUP'S footer. The chart always seems to show the totals for ALL records, rather than just the group whose footer it's in.

Unless there's a trick that anyone knows of to make the chart only show the current GROUPS totals, I'll live with the chart in the REPORT footer.

Thanks again for the nudge that got me this far.

[OBP]

Is the Chart now based on the Query or the Report data?

[Cooee]

Completely on the query now, as per your first good advice.

[OBP]

When you put the Chart on the Report did it allow for any Master/Child Links?
Are Master/Child Links in the Properies?
If they are try setting them to whatever you use for the Grouping on the Main report and the same value in the Chart.

[Cooee]

I don't fully understand what these fields do, but I have changed them to correspond with the 'break' fields and it seems to be heading in the right direction.

Again, I think you've hit on the problem area.

I'm confident that if I continue to probe around in this area it'll come good.

Thanks again for your insight.