Creating lot of charts

[kohai]

Hi,

I have a database where I store many (100's) index values for series of
portfolios. I've been managing the data without too much of a problem, but
having the data in tables doesn't help in the analysis. I need to see charts
to look at trends and the like. Basically simple line charts.

I've been trying to create a report with chart(s) and Access, but it just
seems so difficult. All I'm looking for is the ability to have multiple
charts on a page so that each chart is fed from a different portfolio. The
queried data would look something like this:

Date Name IndexVal
1/1 ABC 100.0
1/2 ABC 101.5
1/3 ABC 101.0
1/4 ABC 98.9
.......
12/31 ABC 112.4
1/1 DEF 100.0
1/2 DEF 98.6
1/3 DEF 98.1
.......
12/31 DEF 71.9
1/1 XYZ 100
1/2 XYZ 105.7
etc.........

I have a feeling I can get more robust charts through a 3rd party
application, but not sure which one would be best. Does anyone have any
recommendations as to how I should proceed? I could end up dealing with
1000's of charts along the way and having to set each one individually would
not be feasable.

Thank you.

kohai

 

[Duane Hookom]

You should be able to create a report based on each unique index name. Add a
chart to the detail section of the report and use the Link Master/Child
properties to display a graph for each index.

 

[kohai]

Duane,

Thanks for the reply. Would that mean that I could only have 1 chart per
page? Or would that same setup allow me to have multiple indices on a page.
I usually like to have 3x2 or 3x3. I don't need all of the charts to take up
a full page, especially since I need to look at so many of them.

Thank you.

 

[Duane Hookom]

It isn't clear whether all graphs are the same other than being limited
different indices. Can you apply a different index filter to a chart and
basically they are all the same? Or, are some unique? Are there repeating
patterns of charts or just a single "pattern"?

 

[kohai]

Each chart would show a different index. Most are over the same time period,
but that does change for some of them (some with more history than others).
Other than the time difference (dates would be on X axis, Index scale on Y)
the only other difference would be that for the Y axis where some indices
perform differently and the scaling would need to adjust to the values
appropriately.

I do a lot of charting in Excel where chart functionality is easier to
control, but with potentially so many charts to create, I need to find a way
to relatively easily get the data from the db into charts faster.

But the size of the chart would be fixed so that I can get as many on the
page that still is readable and spread it over as many pages needed to
include them all.

Thanks again for all of your help.

kohai

 

[Duane Hookom]

The method that I suggested should work as long as you set the charts to
automatically determine the scales. Did you ever try it?

 

[kohai]

I can't seem to get the row source syntax correct, nor will it allow me to do
anything with the link Child/Link Master options as I get the message "Can't
build a
link between unbound forms".

Any suggestions on what syntax I should put for these chart options?

 

[Duane Hookom]

Your main report should have a record source of each unique index. Add the
index field to your chart's row source. Then type the field names into the
Link Master/Child properties.

What is your Row Source?

 

[kohai]

I think this is where I'm having my biggest problem.

My query to pull in index values for each group looks something like this:

Select Portfolio,dDate,PIndex
FROM Indices
WHERE ((([Indices].dDate)>=#9/1/2005#))
ORDER BY [Indices].Portfolio, [Indices].dDate;

I'm using a shorter term now as a sample, but would like to include more
history.

If I have a total of 18 portfolios in my sample, I am trying to get 6 charts
on a page for 3 pages.
I just can't seem to get one (or more) chart(s) to only read just one
portfolio w/o specifically modifying the Row source.

Thanks again for all of your assistance!

 

[Duane Hookom]

You provided the Row Source for the chart control. The report's Record
Source should be
SELECT DISTINCT Portfolio
FROM Indices;

Then set the Link Master/Child properties to [Portfolio].

Once you kinda have this working, you can change your report layout to 2 or
3 columns.

--
Duane Hookom
MS Access MVP
--

I think this is where I'm having my biggest problem.

My query to pull in index values for each group looks something like this:

Select Portfolio,dDate,PIndex
FROM Indices
WHERE ((([Indices].dDate)>=#9/1/2005#))
ORDER BY [Indices].Portfolio, [Indices].dDate;

I'm using a shorter term now as a sample, but would like to include more
history.

If I have a total of 18 portfolios in my sample, I am trying to get 6
charts
on a page for 3 pages.
I just can't seem to get one (or more) chart(s) to only read just one
portfolio w/o specifically modifying the Row source.

Thanks again for all of your assistance!

Your main report should have a record source of each unique index. Add
the
index field to your chart's row source. Then type the field names into
the
Link Master/Child properties.

What is your Row Source?

 

[kohai]

Duane,

Thank you very much for sticking with me. I made the changes to the chart
row souce and the report record source and added the links child/master, but
now the chart appears blank when viewed in print preview mode.

Does the source query need to be a select query or a crosstab? Or does that
not matter?

What else am I missing to see the data?

Thanks again.

You provided the Row Source for the chart control. The report's Record
Source should be
SELECT DISTINCT Portfolio
FROM Indices;

Then set the Link Master/Child properties to [Portfolio].

Once you kinda have this working, you can change your report layout to 2 or
3 columns.

--
Duane Hookom
MS Access MVP
--

I think this is where I'm having my biggest problem.

My query to pull in index values for each group looks something like this:

Select Portfolio,dDate,PIndex
FROM Indices
WHERE ((([Indices].dDate)>=#9/1/2005#))
ORDER BY [Indices].Portfolio, [Indices].dDate;

I'm using a shorter term now as a sample, but would like to include more
history.

If I have a total of 18 portfolios in my sample, I am trying to get 6
charts
on a page for 3 pages.
I just can't seem to get one (or more) chart(s) to only read just one
portfolio w/o specifically modifying the Row source.

Thanks again for all of your assistance!

Your main report should have a record source of each unique index. Add
the
index field to your chart's row source. Then type the field names into
the
Link Master/Child properties.

What is your Row Source?

--
Duane Hookom
MS Access MVP
--

I can't seem to get the row source syntax correct, nor will it allow me
to
do
anything with the link Child/Link Master options as I get the message
"Can't
build a
link between unbound forms".

Any suggestions on what syntax I should put for these chart options?



:

The method that I suggested should work as long as you set the charts
to
automatically determine the scales. Did you ever try it?

--
Duane Hookom
MS Access MVP
--

Each chart would show a different index. Most are over the same
time
period,
but that does change for some of them (some with more history than
others).
Other than the time difference (dates would be on X axis, Index
scale
on
Y)
the only other difference would be that for the Y axis where some
indices
perform differently and the scaling would need to adjust to the
values
appropriately.

I do a lot of charting in Excel where chart functionality is easier
to
control, but with potentially so many charts to create, I need to
find
a
way
to relatively easily get the data from the db into charts faster.

But the size of the chart would be fixed so that I can get as many
on
the
page that still is readable and spread it over as many pages needed
to
include them all.

Thanks again for all of your help.

kohai

:

It isn't clear whether all graphs are the same other than being
limited
different indices. Can you apply a different index filter to a
chart
and
basically they are all the same? Or, are some unique? Are there
repeating
patterns of charts or just a single "pattern"?

--
Duane Hookom
MS Access MVP
--

Duane,

Thanks for the reply. Would that mean that I could only have 1
chart
per
page? Or would that same setup allow me to have multiple indices
on
a
page.
I usually like to have 3x2 or 3x3. I don't need all of the
charts
to
take
up
a full page, especially since I need to look at so many of them.

Thank you.

:

You should be able to create a report based on each unique index
name.
Add a
chart to the detail section of the report and use the Link
Master/Child
properties to display a graph for each index.

--
Duane Hookom
MS Access MVP


Hi,

I have a database where I store many (100's) index values for
series
of
portfolios. I've been managing the data without too much of a
problem,
but
having the data in tables doesn't help in the analysis. I
need
to
see
charts
to look at trends and the like. Basically simple line charts.

I've been trying to create a report with chart(s) and Access,
but
it
just
seems so difficult. All I'm looking for is the ability to
have
multiple
charts on a page so that each chart is fed from a different
portfolio.
The
queried data would look something like this:

Date Name IndexVal
1/1 ABC 100.0
1/2 ABC 101.5
1/3 ABC 101.0
1/4 ABC 98.9
......
12/31 ABC 112.4
1/1 DEF 100.0
1/2 DEF 98.6
1/3 DEF 98.1
......
12/31 DEF 71.9
1/1 XYZ 100
1/2 XYZ 105.7
etc.........

I have a feeling I can get more robust charts through a 3rd
party
application, but not sure which one would be best. Does
anyone
have
any
recommendations as to how I should proceed? I could end up
dealing
with
1000's of charts along the way and having to set each one
individually
would
not be feasable.

Thank you.

kohai

 

[Duane Hookom]

The query row source can be most select queries including crosstabs. You
must make sure the link master/child fields are in the report recordsource
and the chart row source. There are often "timing" issues that are discussed
at the ACG Soft web site http://ourworld.compuserve.com/homepages/attac-cg/

--
Duane Hookom
MS Access MVP

Duane,

Thank you very much for sticking with me. I made the changes to the chart
row souce and the report record source and added the links child/master,
but
now the chart appears blank when viewed in print preview mode.

Does the source query need to be a select query or a crosstab? Or does
that
not matter?

What else am I missing to see the data?

Thanks again.

You provided the Row Source for the chart control. The report's Record
Source should be
SELECT DISTINCT Portfolio
FROM Indices;

Then set the Link Master/Child properties to [Portfolio].

Once you kinda have this working, you can change your report layout to 2
or
3 columns.

--
Duane Hookom
MS Access MVP
--

I think this is where I'm having my biggest problem.

My query to pull in index values for each group looks something like
this:

Select Portfolio,dDate,PIndex
FROM Indices
WHERE ((([Indices].dDate)>=#9/1/2005#))
ORDER BY [Indices].Portfolio, [Indices].dDate;

I'm using a shorter term now as a sample, but would like to include
more
history.

If I have a total of 18 portfolios in my sample, I am trying to get 6
charts
on a page for 3 pages.
I just can't seem to get one (or more) chart(s) to only read just one
portfolio w/o specifically modifying the Row source.

Thanks again for all of your assistance!




:

Your main report should have a record source of each unique index. Add
the
index field to your chart's row source. Then type the field names into
the
Link Master/Child properties.

What is your Row Source?

--
Duane Hookom
MS Access MVP
--

I can't seem to get the row source syntax correct, nor will it allow
me
to
do
anything with the link Child/Link Master options as I get the
message
"Can't
build a
link between unbound forms".

Any suggestions on what syntax I should put for these chart options?



:

The method that I suggested should work as long as you set the
charts
to
automatically determine the scales. Did you ever try it?

--
Duane Hookom
MS Access MVP
--

Each chart would show a different index. Most are over the same
time
period,
but that does change for some of them (some with more history
than
others).
Other than the time difference (dates would be on X axis, Index
scale
on
Y)
the only other difference would be that for the Y axis where some
indices
perform differently and the scaling would need to adjust to the
values
appropriately.

I do a lot of charting in Excel where chart functionality is
easier
to
control, but with potentially so many charts to create, I need to
find
a
way
to relatively easily get the data from the db into charts faster.

But the size of the chart would be fixed so that I can get as
many
on
the
page that still is readable and spread it over as many pages
needed
to
include them all.

Thanks again for all of your help.

kohai

:

It isn't clear whether all graphs are the same other than being
limited
different indices. Can you apply a different index filter to a
chart
and
basically they are all the same? Or, are some unique? Are there
repeating
patterns of charts or just a single "pattern"?

--
Duane Hookom
MS Access MVP
--

Duane,

Thanks for the reply. Would that mean that I could only have
1
chart
per
page? Or would that same setup allow me to have multiple
indices
on
a
page.
I usually like to have 3x2 or 3x3. I don't need all of the
charts
to
take
up
a full page, especially since I need to look at so many of
them.

Thank you.

:

You should be able to create a report based on each unique
index
name.
Add a
chart to the detail section of the report and use the Link
Master/Child
properties to display a graph for each index.

--
Duane Hookom
MS Access MVP


Hi,

I have a database where I store many (100's) index values
for
series
of
portfolios. I've been managing the data without too much
of a
problem,
but
having the data in tables doesn't help in the analysis. I
need
to
see
charts
to look at trends and the like. Basically simple line
charts.

I've been trying to create a report with chart(s) and
Access,
but
it
just
seems so difficult. All I'm looking for is the ability to
have
multiple
charts on a page so that each chart is fed from a different
portfolio.
The
queried data would look something like this:

Date Name IndexVal
1/1 ABC 100.0
1/2 ABC 101.5
1/3 ABC 101.0
1/4 ABC 98.9
......
12/31 ABC 112.4
1/1 DEF 100.0
1/2 DEF 98.6
1/3 DEF 98.1
......
12/31 DEF 71.9
1/1 XYZ 100
1/2 XYZ 105.7
etc.........

I have a feeling I can get more robust charts through a 3rd
party
application, but not sure which one would be best. Does
anyone
have
any
recommendations as to how I should proceed? I could end up
dealing
with
1000's of charts along the way and having to set each one
individually
would
not be feasable.

Thank you.

kohai