Creating a chart from a Query 1

[ksbigfoot]

I am using MS Access 2003.

I created a query with 3 fields and there is only 3 rows that are pulled back.

I created a report. I inserted a Chart using the wizard.
I then went to the properties of the chart after it was created and changed the Row Source to point to my query that pulls in only the 3 fields.

I set my Link Child Field and Link Master Field to the same ID field.

I don't know how to get my graph to show the data from my query. When I double click on the graph, I get the original data that the wizard created.

Can someone see what I am doing wrong?

Thanks

 

[dhookom]

When I double click on the graph, I get the original data that the wizard created" what data do you see when you run the report?

The chart control seems to "cache" data in its spreadsheet. You can view your row source in datasheet view and copy the records to paste into the datasheet in the chart control design view.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ksbigfoot]

Howdy Duane,

I copied the records into the datasheet control, but I am not sure how to change the legend to point to the headers of the row.
I have 3 columns in my recordset.

But the weird thing is that even though I see one field in the legend area, when I run the report, I only see the title of my graph, I don't see the Pie chart.

 

[dhookom]

What is the SQL view of your Row Source of your chart? You may need to change your chart from displaying down to across or some other similar setting.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ksbigfoot]

Howdy Duane,
I am not sure what you mean with "What is the SQL view of your Row Source of your chart?"
I changed the setting from row to column and vice versa, but with no success. It is displaying only one of the columns

But I thinking it might be something else as when I try to view it in print preview, I don't see the chart at all. Do I have to have a specific reference to something like the Microsoft Excel 11.0 Object Library?

 

[dhookom]

You chart control on your report has a Row Source property. I expect this is a query or SQL Statement. You can select and "build" this property and view the SQL view. Copy this SQL statement and paste it into a reply.

I expect your data might be un-normalized. Do you expect your field/column names to be data labels? For instance if your row source looks anything like:

   SELECT Jan, Feb, Mar
   FROM tblMthTotals

You would have better luck if you used a Row Source of the graph like:

   SELECT "Jan" as Mth, [Jan] As Sales
   FROM tblMthTotals
   UNION ALL
   SELECT "Feb", [Feb]
   FROM tblMthTotals
   UNION ALL
   SELECT "Mar", [Mar]
   FROM tblMthTotals;

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ksbigfoot]

Howdy Duane,

Here is my SQL Statement:

SELECT TotalCashCompensation, RRSPMatch, OtherCompensation FROM qryReport

I changed my query to

SELECT "TotalCashCompensation" as TCash, [TotalCashCompensation] As TotalCash    FROM qryReport    UNION ALL    SELECT "RRSPMatch", [RRSPMatch]    FROM qryReport    UNION ALL SELECT "OtherCompensation", [OtherCompensation]    FROM qryReport;

But I still don't see the pie graph.

 

[dhookom]

Do you have anything in the Link Master Child properties of the chart control?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ksbigfoot]

I have ID in both the Link Master and Child.

 

[dhookom]

Try remove it to see what happpens.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ksbigfoot]

Howdy Duane,

Wow, that worked. I removed the ID from both the Master and Child link and the graph comes up like I would like it.

The only thing I have left to figure out is how do I get my graph to change for every record?

 

[dhookom]

You may need to add the ID field to the union query.

SELECT ID, "TotalCashCompensation" as TCash, [TotalCashCompensation] As TotalCash
FROM qryReport    
UNION ALL
SELECT ID, "RRSPMatch", [RRSPMatch]
FROM qryReport
UNION ALL 
SELECT ID, "OtherCompensation", [OtherCompensation]
FROM qryReport;

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ksbigfoot]

Howdy Duane,

If I could give you more than one star, I would.
This worked for me.

I put the ID on the report, made the ID field invisible. And had my query of the graph point to the ID.

Now the graph is updating based on the record that is selected.

Thanks again for all your help, I can see why you are the MVP of this forum. I appreciate all your patience with me.
ksbigfoot