Graph Column Headings

  • Thread starter Thread starter RJF
  • Start date Start date
R

RJF

I've searched the Web for help on this and can't find anything. Hopefully,
someone out there can help me.

I created a graph with the following SQL Statement:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.read_period;

Row heading is Name
Column heading is read_period
Data is SumOfVolume

The field "read_period" is a text field (i.e. 200801, 200802, 200803...).

The graph looks great, except for the column headings. Instead of getting
the 200801 and so forth, I'm getting 2E+05 in each heading. It appears that
it's reading it as a number. I am sure that the field is text in the table
it comes from.

I've tried formatting the read_period field as text (even though it already
is and that didn't work).

If I change the SQL Statement to:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT "Period " & [read_period];

it shows the column headings as Period 200801, etc.
But having the work "Period" in the heading is not acceptable. Even though
it appears correct when I run the statement in an Access query, once it gets
to the graph, it looks like the format was changed to number.

I hope I explained myself clearly enough. Does anyone know how this is
happening and what I can do to fix it.

Thanks so much.
 
Try:
TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT Left([read_period],4) & " " & Right([Read_Period],2);
 
Hi Duane,

Thank you so much for your quick response.

I can't have a space within the column heading. It needs to be "200801" and
can't be "2008 01". Right now I'm using a SQL statement similar to yours
only putting a dash in the heading, i.e. "2008-01". I'm doing it until I can
figure out a way to get it "right", but it's really not acceptable to the
client.

Can you think of anything else I could try?

Thank you,

--
RJF


Duane Hookom said:
Try:
TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT Left([read_period],4) & " " & Right([Read_Period],2);

--
Duane Hookom
Microsoft Access MVP


RJF said:
I've searched the Web for help on this and can't find anything. Hopefully,
someone out there can help me.

I created a graph with the following SQL Statement:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.read_period;

Row heading is Name
Column heading is read_period
Data is SumOfVolume

The field "read_period" is a text field (i.e. 200801, 200802, 200803...).

The graph looks great, except for the column headings. Instead of getting
the 200801 and so forth, I'm getting 2E+05 in each heading. It appears that
it's reading it as a number. I am sure that the field is text in the table
it comes from.

I've tried formatting the read_period field as text (even though it already
is and that didn't work).

If I change the SQL Statement to:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT "Period " & [read_period];

it shows the column headings as Period 200801, etc.
But having the work "Period" in the heading is not acceptable. Even though
it appears correct when I run the statement in an Access query, once it gets
to the graph, it looks like the format was changed to number.

I hope I explained myself clearly enough. Does anyone know how this is
happening and what I can do to fix it.

Thanks so much.
Click to expand...
Click to expand...
 
Also, I tried using the column heading as row headings to see what would
happen and they came out correct.

I don't understand why the column headings treat the field as a number when
the field is text. But the row headings treat the field as text.

--
RJF


RJF said:
Hi Duane,

Thank you so much for your quick response.

I can't have a space within the column heading. It needs to be "200801" and
can't be "2008 01". Right now I'm using a SQL statement similar to yours
only putting a dash in the heading, i.e. "2008-01". I'm doing it until I can
figure out a way to get it "right", but it's really not acceptable to the
client.

Can you think of anything else I could try?

Thank you,

--
RJF


Duane Hookom said:
Try:
TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT Left([read_period],4) & " " & Right([Read_Period],2);

--
Duane Hookom
Microsoft Access MVP


RJF said:
I've searched the Web for help on this and can't find anything. Hopefully,
someone out there can help me.

I created a graph with the following SQL Statement:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.read_period;

Row heading is Name
Column heading is read_period
Data is SumOfVolume

The field "read_period" is a text field (i.e. 200801, 200802, 200803...).

The graph looks great, except for the column headings. Instead of getting
the 200801 and so forth, I'm getting 2E+05 in each heading. It appears that
it's reading it as a number. I am sure that the field is text in the table
it comes from.

I've tried formatting the read_period field as text (even though it already
is and that didn't work).

If I change the SQL Statement to:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT "Period " & [read_period];

it shows the column headings as Period 200801, etc.
But having the work "Period" in the heading is not acceptable. Even though
it appears correct when I run the statement in an Access query, once it gets
to the graph, it looks like the format was changed to number.

I hope I explained myself clearly enough. Does anyone know how this is
happening and what I can do to fix it.

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
Have you tried concatenating a space to the left or right of the column
heading?

I have run across this issue in the past and I believe I was satisfied with
the dash or underscore or space.

--
Duane Hookom
Microsoft Access MVP


RJF said:
Also, I tried using the column heading as row headings to see what would
happen and they came out correct.

I don't understand why the column headings treat the field as a number when
the field is text. But the row headings treat the field as text.

--
RJF


RJF said:
Hi Duane,

Thank you so much for your quick response.

I can't have a space within the column heading. It needs to be "200801" and
can't be "2008 01". Right now I'm using a SQL statement similar to yours
only putting a dash in the heading, i.e. "2008-01". I'm doing it until I can
figure out a way to get it "right", but it's really not acceptable to the
client.

Can you think of anything else I could try?

Thank you,

--
RJF


Duane Hookom said:
Try:
TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT Left([read_period],4) & " " & Right([Read_Period],2);

--
Duane Hookom
Microsoft Access MVP


:

I've searched the Web for help on this and can't find anything. Hopefully,
someone out there can help me.

I created a graph with the following SQL Statement:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.read_period;

Row heading is Name
Column heading is read_period
Data is SumOfVolume

The field "read_period" is a text field (i.e. 200801, 200802, 200803...).

The graph looks great, except for the column headings. Instead of getting
the 200801 and so forth, I'm getting 2E+05 in each heading. It appears that
it's reading it as a number. I am sure that the field is text in the table
it comes from.

I've tried formatting the read_period field as text (even though it already
is and that didn't work).

If I change the SQL Statement to:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT "Period " & [read_period];

it shows the column headings as Period 200801, etc.
But having the work "Period" in the heading is not acceptable. Even though
it appears correct when I run the statement in an Access query, once it gets
to the graph, it looks like the format was changed to number.

I hope I explained myself clearly enough. Does anyone know how this is
happening and what I can do to fix it.

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
Yes, I tried concatenating a space to the left or a space to the right, then
to both left and right. It acts as though the spaces are not there and I
still get 2E+05 instead of the actual heading. It only seems to read the
field as text when I concatenate something other than a number or a space to
it.

This is crazy. The field is text. Do you think this is a bug in Access?

Thanks so much for your suggestions.
--
RJF


Duane Hookom said:
Have you tried concatenating a space to the left or right of the column
heading?

I have run across this issue in the past and I believe I was satisfied with
the dash or underscore or space.

--
Duane Hookom
Microsoft Access MVP


RJF said:
Also, I tried using the column heading as row headings to see what would
happen and they came out correct.

I don't understand why the column headings treat the field as a number when
the field is text. But the row headings treat the field as text.

--
RJF


RJF said:
Hi Duane,

Thank you so much for your quick response.

I can't have a space within the column heading. It needs to be "200801" and
can't be "2008 01". Right now I'm using a SQL statement similar to yours
only putting a dash in the heading, i.e. "2008-01". I'm doing it until I can
figure out a way to get it "right", but it's really not acceptable to the
client.

Can you think of anything else I could try?

Thank you,

--
RJF


:

Try:
TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT Left([read_period],4) & " " & Right([Read_Period],2);

--
Duane Hookom
Microsoft Access MVP


:

I've searched the Web for help on this and can't find anything. Hopefully,
someone out there can help me.

I created a graph with the following SQL Statement:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.read_period;

Row heading is Name
Column heading is read_period
Data is SumOfVolume

The field "read_period" is a text field (i.e. 200801, 200802, 200803...).

The graph looks great, except for the column headings. Instead of getting
the 200801 and so forth, I'm getting 2E+05 in each heading. It appears that
it's reading it as a number. I am sure that the field is text in the table
it comes from.

I've tried formatting the read_period field as text (even though it already
is and that didn't work).

If I change the SQL Statement to:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT "Period " & [read_period];

it shows the column headings as Period 200801, etc.
But having the work "Period" in the heading is not acceptable. Even though
it appears correct when I run the statement in an Access query, once it gets
to the graph, it looks like the format was changed to number.

I hope I explained myself clearly enough. Does anyone know how this is
happening and what I can do to fix it.

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
I think it is probably a bug in something. The chart control might be the
same one that is used in other Office applications.
--
Duane Hookom
Microsoft Access MVP


RJF said:
Yes, I tried concatenating a space to the left or a space to the right, then
to both left and right. It acts as though the spaces are not there and I
still get 2E+05 instead of the actual heading. It only seems to read the
field as text when I concatenate something other than a number or a space to
it.

This is crazy. The field is text. Do you think this is a bug in Access?

Thanks so much for your suggestions.
--
RJF


Duane Hookom said:
Have you tried concatenating a space to the left or right of the column
heading?

I have run across this issue in the past and I believe I was satisfied with
the dash or underscore or space.

--
Duane Hookom
Microsoft Access MVP


RJF said:
Also, I tried using the column heading as row headings to see what would
happen and they came out correct.

I don't understand why the column headings treat the field as a number when
the field is text. But the row headings treat the field as text.

--
RJF


:

Hi Duane,

Thank you so much for your quick response.

I can't have a space within the column heading. It needs to be "200801" and
can't be "2008 01". Right now I'm using a SQL statement similar to yours
only putting a dash in the heading, i.e. "2008-01". I'm doing it until I can
figure out a way to get it "right", but it's really not acceptable to the
client.

Can you think of anything else I could try?

Thank you,

--
RJF


:

Try:
TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT Left([read_period],4) & " " & Right([Read_Period],2);

--
Duane Hookom
Microsoft Access MVP


:

I've searched the Web for help on this and can't find anything. Hopefully,
someone out there can help me.

I created a graph with the following SQL Statement:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.read_period;

Row heading is Name
Column heading is read_period
Data is SumOfVolume

The field "read_period" is a text field (i.e. 200801, 200802, 200803...).

The graph looks great, except for the column headings. Instead of getting
the 200801 and so forth, I'm getting 2E+05 in each heading. It appears that
it's reading it as a number. I am sure that the field is text in the table
it comes from.

I've tried formatting the read_period field as text (even though it already
is and that didn't work).

If I change the SQL Statement to:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT "Period " & [read_period];

it shows the column headings as Period 200801, etc.
But having the work "Period" in the heading is not acceptable. Even though
it appears correct when I run the statement in an Access query, once it gets
to the graph, it looks like the format was changed to number.

I hope I explained myself clearly enough. Does anyone know how this is
happening and what I can do to fix it.

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
Well, thank you for your help. I guess the client will have to deal with it
the way it is.

Thanks,


--
RJF


Duane Hookom said:
I think it is probably a bug in something. The chart control might be the
same one that is used in other Office applications.
--
Duane Hookom
Microsoft Access MVP


RJF said:
Yes, I tried concatenating a space to the left or a space to the right, then
to both left and right. It acts as though the spaces are not there and I
still get 2E+05 instead of the actual heading. It only seems to read the
field as text when I concatenate something other than a number or a space to
it.

This is crazy. The field is text. Do you think this is a bug in Access?

Thanks so much for your suggestions.
--
RJF


Duane Hookom said:
Have you tried concatenating a space to the left or right of the column
heading?

I have run across this issue in the past and I believe I was satisfied with
the dash or underscore or space.

--
Duane Hookom
Microsoft Access MVP


:

Also, I tried using the column heading as row headings to see what would
happen and they came out correct.

I don't understand why the column headings treat the field as a number when
the field is text. But the row headings treat the field as text.

--
RJF


:

Hi Duane,

Thank you so much for your quick response.

I can't have a space within the column heading. It needs to be "200801" and
can't be "2008 01". Right now I'm using a SQL statement similar to yours
only putting a dash in the heading, i.e. "2008-01". I'm doing it until I can
figure out a way to get it "right", but it's really not acceptable to the
client.

Can you think of anything else I could try?

Thank you,

--
RJF


:

Try:
TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT Left([read_period],4) & " " & Right([Read_Period],2);

--
Duane Hookom
Microsoft Access MVP


:

I've searched the Web for help on this and can't find anything. Hopefully,
someone out there can help me.

I created a graph with the following SQL Statement:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.read_period;

Row heading is Name
Column heading is read_period
Data is SumOfVolume

The field "read_period" is a text field (i.e. 200801, 200802, 200803...).

The graph looks great, except for the column headings. Instead of getting
the 200801 and so forth, I'm getting 2E+05 in each heading. It appears that
it's reading it as a number. I am sure that the field is text in the table
it comes from.

I've tried formatting the read_period field as text (even though it already
is and that didn't work).

If I change the SQL Statement to:

TRANSFORM Sum(qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Volume) AS SumOfVolume
SELECT qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
FROM qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt
GROUP BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name
ORDER BY qry_Mo_Config_Rpt_SmFrm_BW_CopPrnt.Name DESC
PIVOT "Period " & [read_period];

it shows the column headings as Period 200801, etc.
But having the work "Period" in the heading is not acceptable. Even though
it appears correct when I run the statement in an Access query, once it gets
to the graph, it looks like the format was changed to number.

I hope I explained myself clearly enough. Does anyone know how this is
happening and what I can do to fix it.

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top