Chart legend problem ("SumOf")

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using the chart wizard, I have created a line chart based on a linked excel
table. The x-axis is Months, and the y-axis is $.

The table works great, except the legend shows "SumOfName1", "SumOfName2"
etc. where Name1 and Name2 represent the names of each set of data. How can I
customize the legend to get rid of "SumOf"???

I have tried building the chart in a form instead of a report, but when I
change the column name from "SumOfName1" to "Name1" in datasheet view it
loses the change when I toggle back and forth between design and form views,
even if I save the form immediately after the change.

I have also tried creating a query from my linked excel table, and then
using the query to build the chart but I still have the same problem.

I am relatively new to access and creating charts in particular, so maybe
I'm missing something relatively easy. Any and all suggestions are greatly
appreciated!
 
Find and modify the Row Source property of your chart control. You can
create almost any "alias" you want for your column headings in the Row
Source. These should flow through to labels in the chart.
 
That worked great, and the legend is now correct. However, now my months
(x-axis) are being listed alphabetically instead of chronologically (so
instead of Jan, Feb, March... I'm now getting April, August, December...)

This was my SQL statement from the Row Source before the change:
SELECT (Format([Month],"mmm"" '""yy")) AS Expr1, Sum(MonthlyCvR.ProjAVCosts)
AS SumOfProjAVCosts, Sum(MonthlyCvR.ProjAVRev) AS SumOfProjAVRev,
Sum(MonthlyCvR.ProjPartnerCosts) AS SumOfProjPartnerCosts,
Sum(MonthlyCvR.ProjPartnerRev) AS SumOfProjPartnerRev
FROM MonthlyCvR
GROUP BY (Format([Month],"mmm"" '""yy")), (Year([Month])*12+Month([Month])-1);

This is my SQL Statement after the change:
SELECT (Format([Month],"mmm"" '""yy")) AS Expr1, Sum(MonthlyCvR.ProjAVCosts)
AS [AV Costs], Sum(MonthlyCvR.ProjAVRev) AS [AV Rev],
Sum(MonthlyCvR.ProjPartnerCosts) AS [PA Cost], Sum(MonthlyCvR.ProjPartnerRev)
AS [PA Rev]
FROM MonthlyCvR
GROUP BY (Format([Month],"mmm"" '""yy")), (Year([Month])*12+Month([Month])-1);

How can I get my months back to chronological order? The start month is
dynamic based on user selection in another form.
 
Set the Row Source to a query that sorts the way you want. Try something
like:

SELECT (Format([Month],"mmm"" '""yy")) AS Expr1,
Sum(MonthlyCvR.ProjAVCosts) AS [AV Costs],
Sum(MonthlyCvR.ProjAVRev) AS [AV Rev],
Sum(MonthlyCvR.ProjPartnerCosts) AS [PA Cost],
Sum(MonthlyCvR.ProjPartnerRev)
AS [PA Rev]
FROM MonthlyCvR
GROUP BY Format([Month],"yyyymm"), (Format([Month],"mmm"" '""yy")),
(Year([Month])*12+Month([Month])-1)
ORDER BY Format([Month],"yyyymm");

--
Duane Hookom
MS Access MVP


SteveS said:
That worked great, and the legend is now correct. However, now my months
(x-axis) are being listed alphabetically instead of chronologically (so
instead of Jan, Feb, March... I'm now getting April, August, December...)

This was my SQL statement from the Row Source before the change:
SELECT (Format([Month],"mmm"" '""yy")) AS Expr1,
Sum(MonthlyCvR.ProjAVCosts)
AS SumOfProjAVCosts, Sum(MonthlyCvR.ProjAVRev) AS SumOfProjAVRev,
Sum(MonthlyCvR.ProjPartnerCosts) AS SumOfProjPartnerCosts,
Sum(MonthlyCvR.ProjPartnerRev) AS SumOfProjPartnerRev
FROM MonthlyCvR
GROUP BY (Format([Month],"mmm"" '""yy")),
(Year([Month])*12+Month([Month])-1);

This is my SQL Statement after the change:
SELECT (Format([Month],"mmm"" '""yy")) AS Expr1,
Sum(MonthlyCvR.ProjAVCosts)
AS [AV Costs], Sum(MonthlyCvR.ProjAVRev) AS [AV Rev],
Sum(MonthlyCvR.ProjPartnerCosts) AS [PA Cost],
Sum(MonthlyCvR.ProjPartnerRev)
AS [PA Rev]
FROM MonthlyCvR
GROUP BY (Format([Month],"mmm"" '""yy")),
(Year([Month])*12+Month([Month])-1);

How can I get my months back to chronological order? The start month is
dynamic based on user selection in another form.

Duane Hookom said:
Find and modify the Row Source property of your chart control. You can
create almost any "alias" you want for your column headings in the Row
Source. These should flow through to labels in the chart.
Click to expand...
Click to expand...
 
I have same issue--and I don't know where to find "row source" to change the
legend/asix headings. Also, I would like to show the amounts associated with
the axis--that is, show the total $ by/above each bar on the chart--how to
do??
Thanks.




Duane Hookom said:
Set the Row Source to a query that sorts the way you want. Try something
like:

SELECT (Format([Month],"mmm"" '""yy")) AS Expr1,
Sum(MonthlyCvR.ProjAVCosts) AS [AV Costs],
Sum(MonthlyCvR.ProjAVRev) AS [AV Rev],
Sum(MonthlyCvR.ProjPartnerCosts) AS [PA Cost],
Sum(MonthlyCvR.ProjPartnerRev)
AS [PA Rev]
FROM MonthlyCvR
GROUP BY Format([Month],"yyyymm"), (Format([Month],"mmm"" '""yy")),
(Year([Month])*12+Month([Month])-1)
ORDER BY Format([Month],"yyyymm");

--
Duane Hookom
MS Access MVP


SteveS said:
That worked great, and the legend is now correct. However, now my months
(x-axis) are being listed alphabetically instead of chronologically (so
instead of Jan, Feb, March... I'm now getting April, August, December...)

This was my SQL statement from the Row Source before the change:
SELECT (Format([Month],"mmm"" '""yy")) AS Expr1,
Sum(MonthlyCvR.ProjAVCosts)
AS SumOfProjAVCosts, Sum(MonthlyCvR.ProjAVRev) AS SumOfProjAVRev,
Sum(MonthlyCvR.ProjPartnerCosts) AS SumOfProjPartnerCosts,
Sum(MonthlyCvR.ProjPartnerRev) AS SumOfProjPartnerRev
FROM MonthlyCvR
GROUP BY (Format([Month],"mmm"" '""yy")),
(Year([Month])*12+Month([Month])-1);

This is my SQL Statement after the change:
SELECT (Format([Month],"mmm"" '""yy")) AS Expr1,
Sum(MonthlyCvR.ProjAVCosts)
AS [AV Costs], Sum(MonthlyCvR.ProjAVRev) AS [AV Rev],
Sum(MonthlyCvR.ProjPartnerCosts) AS [PA Cost],
Sum(MonthlyCvR.ProjPartnerRev)
AS [PA Rev]
FROM MonthlyCvR
GROUP BY (Format([Month],"mmm"" '""yy")),
(Year([Month])*12+Month([Month])-1);

How can I get my months back to chronological order? The start month is
dynamic based on user selection in another form.

Duane Hookom said:
Find and modify the Row Source property of your chart control. You can
create almost any "alias" you want for your column headings in the Row
Source. These should flow through to labels in the chart.

--
Duane Hookom
MS Access MVP

Using the chart wizard, I have created a line chart based on a linked
excel
table. The x-axis is Months, and the y-axis is $.

The table works great, except the legend shows "SumOfName1",
"SumOfName2"
etc. where Name1 and Name2 represent the names of each set of data. How
can I
customize the legend to get rid of "SumOf"???

I have tried building the chart in a form instead of a report, but when
I
change the column name from "SumOfName1" to "Name1" in datasheet view
it
loses the change when I toggle back and forth between design and form
views,
even if I save the form immediately after the change.

I have also tried creating a query from my linked excel table, and then
using the query to build the chart but I still have the same problem.

I am relatively new to access and creating charts in particular, so
maybe
I'm missing something relatively easy. Any and all suggestions are
greatly
appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top