Charts/Queries/Reports: Aaarrrgggghhhh!

[Raistlin]

They're all driving me insane.....

OK, here's the problem (sorry if this is a long one, if it's in the wrong
group then please let me know):

I have a table, lets call it "Item Percentages". It contains the following
fields:

1. Percentage ID (Auto number, Primary Key)
2. Item Category (This is a lookup field, 10 different values, linked to
another table that gives more information about the categories)
3. Date (This is a date field, format mmm yyyy, there can be more than
one date for each category but none of the dates are repeated within a
category)
4. Percentage (text field, let's call this the percentage of requests for
that category that were available from stock in the month given in the Date
field).

I've created a form that shows the above information with the
dates/percentages as a table (subreport) and also as a chart. Moving
backwards and forwards between the records selects a different category,
updates the table and updates the chart.
However, the chart (and the table) show all of the percentages and dates
that are input for each category. What I need to do is specify using
another form what range of dates these two items will show. I have a form
for this purpose that has two boxes for entering dates (Report Start Date
and Report End Date). I know I need to get the chart to look up these two
values and then only display dates and percentages between those two dates
but I can't for the life of me get it to work.

The next thing that I need to do is create a report based around this chart
that includes a few other bits of information but will use the same method
of determining the dates as the original chart.
The only way I've been able to get the chart to display the date range that
I want is to go into it in design view and alter the scale. As you can
imagine this is becoming a pain and those changes don't (obviously) affect
the scales in the report charts.

I'm not sure if the above is all that clear, if you need any further
information please let me know.

Thanks in advance for any/all help, which will be gratefully received.

The Version of Access that I'm using is on an NT platform so I'm assuming
that it's something like Access 97, not positive though.

Regards,

Rob.

 

[Guest]

Rob,
The "usual" M.O. is to use a query for the chart that references the
textboxes on a form.
Then a command button calls for the report. Here's a sql string from this
type of query:

SELECT tblSales.DepartmentName, tblSales.SalesAmount, tblSales.Edate
FROM tblSales
WHERE (((tblSales.Edate) Between [Forms]![frmReportDates]![txtBegin] And
[Forms]![frmReportDates]![txtEnd]))

Steve

Here's my standard advice for making graphs (charts) in Access:

Some instructions for a using MsGraph with Access.

The Chart Wizard in Access leaves much to be desired,
ie: you're not limited to 6 Series.

Take a blank Form (even if you're making a graph for a Report)
and insert an unbound object frame.
With the form in design mode, Insert/Object (on the menus),
select Ms Graph (*Not* the Chart Wizard)

Click on the detail of your Form (ie: click off the graph)

Open the properties of the Graph object:
Format Tab: Size Mode - Zoom
Column Heads - Yes
Data Tab: Row Source Type - Table/Query
Row Source - Enter the name of your table, CrossTab Query, Select Query or
SQL
Enabled - Yes
Locked - No

View the form then go right back to design view (this will load
the graph datasheet with your data vs the sample data).
Don't worry about what the graph looks like just yet.

Then right click the Graph and select Chart Object ->Open (not Edit)

Make sure the Data/Series is in Rows or Columns - whatever is
appropriate for your data.

Now you have something to work with. Select the graph type
and any formatting you wish.

The Row Source can be a table or query name or a sql statement which
makes it very nice for dynamic data.

Using automation you can dynamically change the graph
to suit your needs.

If you're *really* making a report, now copy the Graph object
from the form to the report.


Download the Graph Sample MDB from MsKb Q186855, Grphsm97.Exe
and convert it to a newer version of Access if req'd.

Study the Graph Help file Graph9.hlp (A97) or Graph9.chm (A2k)
Study the Graph object Methods and Properties in the Access Object Browser

Also get the automation help files appropriate for you
for various help in coding:

Q302460 Office XP Products
Q260410 Office 2000 Products
Q167223 Office 97 Products

 

[Raistlin]

Hi Steve,

I'll give all of that a try, thanks very much for the help, I'll let you
know how I get on.

Rob.

Rob,
The "usual" M.O. is to use a query for the chart that references the
textboxes on a form.
Then a command button calls for the report. Here's a sql string from this
type of query:

SELECT tblSales.DepartmentName, tblSales.SalesAmount, tblSales.Edate
FROM tblSales
WHERE (((tblSales.Edate) Between [Forms]![frmReportDates]![txtBegin] And
[Forms]![frmReportDates]![txtEnd]))

Steve

<SNIP>

 

[Raistlin]

Hi Steve,

Just a quick note to say thanks again for the advice, I've got it sussed
now.

I sorted out the query which looked at the dates on the form, unfortunately
it never occurred to me that the form would need to be open for the query to
be able to read from it (Doh!), I now have an "OnClick" macro that opens the
form and hides it before the query runs. All working hunky dory now.

Cheers again,

Rob.

Rob,
The "usual" M.O. is to use a query for the chart that references the
textboxes on a form.
Then a command button calls for the report. Here's a sql string from this
type of query:

SELECT tblSales.DepartmentName, tblSales.SalesAmount, tblSales.Edate
FROM tblSales
WHERE (((tblSales.Edate) Between [Forms]![frmReportDates]![txtBegin] And
[Forms]![frmReportDates]![txtEnd]))

Steve

<SNIP>