creating chart on report from subset of data

[gtrager]

I am trying to create a series of charts on a report that
has data broken down by categories. The data comes from a
query from a larger database.

The basic format of the report looks like:

Analyte
Formulation
days potency

where the data come from a query that returns records in
the form of:

Analyte, Formulation, days, potency

The report then groups by Analyte and Formulation

What I want to do is insert a scatterplot for each unique
group of Analyte X Formulation that plots data for days X
potency in a small chart.

The online help is useless to tell me how to do what
should be a relatively simple thing.

Has anyone done this before?

 

[Duane Hookom]

I recommend creating all charts on forms. When you get done (or mostly done)
you can copy the control from the form to your report. If I understand
correctly, your chart can take advantage of the Link Master Child properties
just like a subreport would.

I hope this gets you moving in the right direction. Come on back to this
thread with additional questions.

 

[gtrager]

forgive my ignorance, as I'm not all that experienced at
this, but how does creating a chart on a form differ from
doing it on a report?

I'm ignoratnt enough that I can only figure out how to
create a form that displays one record at a time, whereas
(via wizard anyhow), I can easily create a report that
gives me a tabular listing of the data I'm seeking for all
records in a set (in my example a table of potency for
each day) and have these grouped into subsets by
formulation and analyte.

When I try to put a chart into this, I can get as far as a
chart that plots ALL combinations of potecy and day, but I
can't tell teh thing how to only plot the particular day X
potecy info for a particular analyte X formulation
combination.

I have three analytes and 18 formulations, so what I'm
looking for in the end is a report that has 54 separate
sections, with one chart in each one.

so let me back the train up one stop here. How can I make
a form that displays all the data for a perticular subset
of days X potency?

the database is put together this way:

analyte_start

|analyte | |ID |
|start_date | (1)---------->(many) |analyte_start_ID |
|formulation_code |
|date_sampled |
|potency |

days elapsed is calculated in a query using datediff
between date_sampled and start_date.

the query used to construct my report, SQL is:

SELECT Analyte_Start.Analyte, Analyte_Start.Start_Date,
Data_Table.[Formulation _Code], Data_Table.Date_Sampled,
DateDiff("y",[Analyte_Start]![Start_Date],[Data_Table]!
[Date_Sampled]) AS days_elapsed, Data_Table.Potency
FROM Analyte_Start INNER JOIN Data_Table ON
Analyte_Start.ID = Data_Table.Analyte_Start_ID
ORDER BY Analyte_Start.Analyte, Data_Table.[Formulation
_Code];

BY using the wizard, with this query as the data source,
teh basic report was easily constructed. WHat I can't
figure out is how to limit the subset of data in the chart
to just a particular subset of this data. Simply placing
it on the report in a certain place does not restrict it's
scope.

Thanks for the help.

 

[Duane Hookom]

I didn't see anything in your reply that mentions you attempted to set the
Link Master/Child properties. Setting these properties can essentially
filter the graph to the values of your fields in the section of the report.

There are some problems with the graph data "lagging behind" the report data
with Access 2000.

Come on back once you have tried this if you have any issues.

--
Duane Hookom
MS Access MVP

forgive my ignorance, as I'm not all that experienced at
this, but how does creating a chart on a form differ from
doing it on a report?

I'm ignoratnt enough that I can only figure out how to
create a form that displays one record at a time, whereas
(via wizard anyhow), I can easily create a report that
gives me a tabular listing of the data I'm seeking for all
records in a set (in my example a table of potency for
each day) and have these grouped into subsets by
formulation and analyte.

When I try to put a chart into this, I can get as far as a
chart that plots ALL combinations of potecy and day, but I
can't tell teh thing how to only plot the particular day X
potecy info for a particular analyte X formulation
combination.

I have three analytes and 18 formulations, so what I'm
looking for in the end is a report that has 54 separate
sections, with one chart in each one.

so let me back the train up one stop here. How can I make
a form that displays all the data for a perticular subset
of days X potency?

the database is put together this way:

analyte_start

|analyte | |ID |
|start_date | (1)---------->(many) |analyte_start_ID |
|formulation_code |
|date_sampled |
|potency |

days elapsed is calculated in a query using datediff
between date_sampled and start_date.

the query used to construct my report, SQL is:

SELECT Analyte_Start.Analyte, Analyte_Start.Start_Date,
Data_Table.[Formulation _Code], Data_Table.Date_Sampled,
DateDiff("y",[Analyte_Start]![Start_Date],[Data_Table]!
[Date_Sampled]) AS days_elapsed, Data_Table.Potency
FROM Analyte_Start INNER JOIN Data_Table ON
Analyte_Start.ID = Data_Table.Analyte_Start_ID
ORDER BY Analyte_Start.Analyte, Data_Table.[Formulation
_Code];

BY using the wizard, with this query as the data source,
teh basic report was easily constructed. WHat I can't
figure out is how to limit the subset of data in the chart
to just a particular subset of this data. Simply placing
it on the report in a certain place does not restrict it's
scope.

Thanks for the help.

-----Original Message-----
I recommend creating all charts on forms. When you get done (or mostly done)
you can copy the control from the form to your report. If I understand
correctly, your chart can take advantage of the Link Master Child properties
just like a subreport would.

I hope this gets you moving in the right direction. Come on back to this
thread with additional questions.

--
Duane Hookom
MS Access MVP





.