bar chart problems

[irethedo]

I have never set up a bar chart before and I am having some problems getting it right- please help if you can...

I am trying to add a horizontal bar chart to a report and
I am using the Wizard but what shows up on the preview window does not look like the end result ...

I have 2 fields in a query, one is the date and the other is rework time. I wish to launch the report from a form in which I specify the start and end dates and then I want the report to show the sum total of rework hours performed
in each month within the start and end dates.

The form is set up for the start and end date and this query works just fine but on the report in design mode, the chart is shown with 3 groups of 4 bars labeled East, West, & North....

And when I open the report in normal mode this chart box is totally blank.

What is going on with this or better yet, what am I doing wrong?

I would like to be able to display each month's rework totals between the start andend date but I need some help in setting this chart up.


Thank you in advance

 

[dhookom]

The key is the Row Source of your chart. This must be set up to display the data you want to graph. View the Row Source in datasheet and copy the records to your clipboard. Then close the datasheet and open the design view or your chart control. View its datasheet a paste your data over the chart datasheet.

I'm not sure why your chart is blank. Is your form open with criteria values entered? Does your chart control have any values in its Link Master/Child properties?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

Thanks Duane-

I am still not sure what data to enter into the chart and have typed what I am seeing next to each of your instructions below- sorry that I am not picking it up that quickly....

DH:
The key is the Row Source of your chart. This must be set up to display the data you want to graph.

View the Row Source in datasheet and copy the records to your clipboard.

my response:
===============
The Row Source Type is:
Table/Query

The Row Source is:
SELECT (Format([Date],"MMM 'YY")),Sum([Rework]) AS [SumOfRework] FROM [FPY Table] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY"));

The Link Child and Link Maser Fields is Date
===============

DH:
Then close the datasheet and open the design view or your chart control.

View its datasheet and paste your data over the chart datasheet.

my response:
==============
I have 2 charts that I made to test this but I am not sure what data to put into the chart. In the page footer of my report, the chart control datasheet appears as a spread sheet with 5 columns:

1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
East 20.4 27.4 90 20.4
West 30.6 38.6 34.6 31.6
North 45.9 469 45 43.9

This chart has a chart title of "FPY Table" and when I view the report this shows up as a blank box with onth the chart title showing.

Then I have made another chart and placed it in the Report Footer section with the same Row source information as in the other chart but this one has the chart title "rework qry". In this control's data sheet I made row 1 with a blank column then Jan, Feb, Mar, April ... (etc) for each month in each column. And this control shows up as a blank with only the chart title
==============

DH:
I'm not sure why your chart is blank. Is your form open with criteria values entered? Does your chart control have any values in its Link Master/Child properties?

my response:
============
I am not sure what you mean by criteria values entered but I am beginning to wonder if it is picking up the date or not... I launch this report from my form which contains a start and end date but the query is set to:
"Format([Date],"MMM 'YY"))," but I am assuming that because the date range is already handled by the sum action of the rework- this may be the problem but if it is I am not sure how to fix it- I set the criteria into the date "Between [forms]![ADMIN form]![StartDate] And [forms]![ADMIN form]![EndDate]" but still no go

Also- the Link Master/Child properties only contain date.

I am so confused...

Thanks again
============

 

[dhookom]

It shouldn't be too difficult to copy and paste your row source records over the datasheet records in your chart design view.

Remove the [Date] from the link master child and see what values are returned in your chart.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

Thanks Duane-

You right- It shouldn't be too difficult to copy and paste your row source records over the datasheet records in my chart design view...

The problem is that I do not know which records, where to find these, or how to copy them over (format)-

The Row source looks like this:

SELECT (Format([Date],"mmm"" '""yy")) AS Expr1, Sum([rework qry].Rework) AS SumOfRework
FROM [rework qry]
GROUP BY (Format([Date],"mmm"" '""yy")), (Year([Date])*12+Month([Date])-1);


and the datasheet looks like a spread sheet so I am unsure what to put in the columns and rows of the data sheet.

Sorry to be so thick headed about this but I am not sure how to do what you suggested...

I did remove the date from the child and parent links and there is no difference- still blank boxes instead of charts.


Thank you for your patience

 

[dhookom]

You select and copy the "datasheet looks like a spread sheet" to your clipboard. You then go to the datasheet view of the graph/chart control. Delete the datasheet records there and paste yout "datasheet looks like a spread sheet".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

Thanks Duane-

I must be missing something here because I did all that but still no cigar...

Here is what I have:

The row source for the chart =
SELECT (Format([Date],"mmm"" '""yy")) AS Expr1, Sum([rework qry].Rework) AS SumOfRework
FROM [rework qry]
GROUP BY (Format([Date],"mmm"" '""yy")), (Year([Date])*12+Month([Date])-1);

The datasheet view of the chart=

| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep
Rework | | | | | | | | |

and when I look at the form in "print view" all I get is a blank box with the chart title..

Do I need to fill in the datasheet with values under each of the columns (labeled by the month) or does the query do this for me?

If I do need to add some data in these cells, what data do I enter here?

 

[dhookom]

It doesn't look like your row source sql matches your datasheet view. You mention "form in print preview" when I thought this was a report.

What is this "add some data in these cells" come from? Your Row Source provides value for graphing.

Does your datasheet view of your row source actually contain some real values? It seems I am having trouble asking questions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

Ok Duane-
I apologize if I am not explaining myself well enough and that I am not sure what I need to do...

DH:
It doesn't look like your row source sql matches your datasheet view. You mention "form in print preview" when I thought this was a report.

-Sorry typo- I meant report in print review...

I am trying to get a bar chart that will dislay the total number of hours reworked within a given month, per month, within a time frame as defined by the start and end dates of the form which launches the report. So that if a user types in a start date of 1/1/06 and an end date of 5/30/06, I would like the chart to show the total rework for each month (Jan - May) with each month a seperate bar in the graph- and that is all- should be simple right?

Given that this is what I want to display in the report, what should the row source information look like?


DH:
What is this "add some data in these cells" come from? Your Row Source provides value for graphing.

- In the data sheet view of the chart that was created by use of the wizard, there was columns and rows (which appear like a spreadsheet) that looked like this:

1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
East 20.4 27.4 90 20.4
West 30.6 38.6 34.6 31.6
North 45.9 469 45 43.9


when I changed the information in the data sheet as you recommended, I chaged it ot the following:

| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug |
Rework | | | | | | | | |

my question was whether or not I need to add any values to the rework row under each column or is that filled by the query?


DH:
Does your datasheet view of your row source actually contain some real values? It seems I am having trouble asking questions.

- I want the data sheet to be filled with data from the query but I am not sure how to get from the data sheet information created by the wizard so that the data sheet view includes only the data that I want the chart to display...

I know what I want the chart to display but I am not sure how explain it or how to do it...

Thank you once again for your patience and helpful suggestions

 

[dhookom]

Your chart's Row Source property would be a query or SQL statement like:
[tt][blue]
SELECT Format([Date],"mmm"" '""yy") AS Mth, Sum([rework qry].Rework) AS [Total Rework]
FROM [rework qry]
GROUP BY Format([Date],"mmm"" '""yy"), (Year([Date])*12+Month([Date])-1)
ORDER BY (Year([Date])*12+Month([Date])-1);
[/blue][/tt]
I would expect the datasheet view of your Row Source to look like:
[tt][blue]
Mth Total Rework
May '92 695.12
Aug '92 1960.01
Apr '92 731.79
May '93 2135.49
Oct '93 531.85
Oct '93 5996.49
Jan '94 4733.23
Mar '94 125.63
Nov '94 5176.65
[/blue][/tt]

You would copy these records and paste them into the datasheet of your chart control design view.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]