Sorting graph columns

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

RJF

Access 2000

Problem: Sorting the columns in a graph in ascending order according to the
"read_period" field, not the "Period" field.

Purpose: Show the comparison between the prior year data and the current
year data.

Name read_period Amounts Period
PRIOR ACTUAL 200712 758574 Period 12
PRIOR ACTUAL 200713 788824 Period 13
PRIOR ACTUAL 200801 699768 Period 01
PRIOR ACTUAL 200802 901306 Period 02

ACTUAL 200812 680414 Period 12
ACTUAL 200813 612171 Period 13
ACTUAL 200901 545913 Period 01
ACTUAL 200902 848277 Period 02

PRIOR AVERAGE 200712 770808 Period 12
PRIOR AVERAGE 200713 770808 Period 13
PRIOR AVERAGE 200801 770808 Period 01
PRIOR AVERAGE 200802 770808 Period 02

AVERAGE 200812 767428 Period 12
AVERAGE 200813 767428 Period 13
AVERAGE 200901 767428 Period 01
AVERAGE 200902 767428 Period 02

Here’s the Row Source that I am using:

TRANSFORM Sum(qry_BW_ExpVol_UNION.EXPECT_VOL) AS SumOfEXPECT_VOL

SELECT qry_BW_ExpVol_UNION.Name

FROM qry_BW_ExpVol_UNION

GROUP BY qry_BW_ExpVol_UNION.Organize, qry_BW_ExpVol_UNION.Name

ORDER BY qry_BW_ExpVol_UNION.Organize, "Period " & Right([read_period],2)
PIVOT "Period " & Right([read_period],2);

Result Wanted:

Name Period 12 Period 13 Period 01 Period 02
ACTUAL 680414 612171 545913 848277
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 758574 788824 699768 901306
PRIOR AVERAGE 770808 770808 770808 770808

Actual Result:

Name Period 01 Period 02 Period 12 Period 13
ACTUAL 545913 848277 680414 612171
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 699768 901306 758574 788824
PRIOR AVERAGE 770808 770808 770808 770808

Any suggestions would be appreciated.

Thank you,
Rach
 
Try changing PIVOT statement to read like this --
PIVOT "Period " & Right([read_period],2) In("Period 12", "Period 13",
"Period 01", "Period 02");
 
Hi Karl,

Thanks so much for the quick response. That works great for this particular
senario. But the Periods could always change on the fly. Sometimes the
underlying query will be Period 12 through Period 02, but it could also be
Period 04 through Period 03. (Period 04 being for 2008 and Period 03 being
for 2009 or for 2007 and 2008) I have a table that holds the Period range
and that is what the underlying query (qry_BW_ExpVol_UNION) is based on.

Is there any way to do this in the correct order if you don't know the range
up front?

Thanks,
Rach


--
RJF


KARL DEWEY said:
Try changing PIVOT statement to read like this --
PIVOT "Period " & Right([read_period],2) In("Period 12", "Period 13",
"Period 01", "Period 02");

--
KARL DEWEY
Build a little - Test a little


RJF said:
Access 2000

Problem: Sorting the columns in a graph in ascending order according to the
"read_period" field, not the "Period" field.

Purpose: Show the comparison between the prior year data and the current
year data.

Name read_period Amounts Period
PRIOR ACTUAL 200712 758574 Period 12
PRIOR ACTUAL 200713 788824 Period 13
PRIOR ACTUAL 200801 699768 Period 01
PRIOR ACTUAL 200802 901306 Period 02

ACTUAL 200812 680414 Period 12
ACTUAL 200813 612171 Period 13
ACTUAL 200901 545913 Period 01
ACTUAL 200902 848277 Period 02

PRIOR AVERAGE 200712 770808 Period 12
PRIOR AVERAGE 200713 770808 Period 13
PRIOR AVERAGE 200801 770808 Period 01
PRIOR AVERAGE 200802 770808 Period 02

AVERAGE 200812 767428 Period 12
AVERAGE 200813 767428 Period 13
AVERAGE 200901 767428 Period 01
AVERAGE 200902 767428 Period 02

Here’s the Row Source that I am using:

TRANSFORM Sum(qry_BW_ExpVol_UNION.EXPECT_VOL) AS SumOfEXPECT_VOL

SELECT qry_BW_ExpVol_UNION.Name

FROM qry_BW_ExpVol_UNION

GROUP BY qry_BW_ExpVol_UNION.Organize, qry_BW_ExpVol_UNION.Name

ORDER BY qry_BW_ExpVol_UNION.Organize, "Period " & Right([read_period],2)
PIVOT "Period " & Right([read_period],2);

Result Wanted:

Name Period 12 Period 13 Period 01 Period 02
ACTUAL 680414 612171 545913 848277
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 758574 788824 699768 901306
PRIOR AVERAGE 770808 770808 770808 770808

Actual Result:

Name Period 01 Period 02 Period 12 Period 13
ACTUAL 545913 848277 680414 612171
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 699768 901306 758574 788824
PRIOR AVERAGE 770808 770808 770808 770808

Any suggestions would be appreciated.

Thank you,
Rach
Click to expand...
Click to expand...
 
Search on post by Duane Hookum. I think I have seen something on dynamic
crosstab column headings.
--
KARL DEWEY
Build a little - Test a little


RJF said:
Hi Karl,

Thanks so much for the quick response. That works great for this particular
senario. But the Periods could always change on the fly. Sometimes the
underlying query will be Period 12 through Period 02, but it could also be
Period 04 through Period 03. (Period 04 being for 2008 and Period 03 being
for 2009 or for 2007 and 2008) I have a table that holds the Period range
and that is what the underlying query (qry_BW_ExpVol_UNION) is based on.

Is there any way to do this in the correct order if you don't know the range
up front?

Thanks,
Rach


--
RJF


KARL DEWEY said:
Try changing PIVOT statement to read like this --
PIVOT "Period " & Right([read_period],2) In("Period 12", "Period 13",
"Period 01", "Period 02");

--
KARL DEWEY
Build a little - Test a little


RJF said:
Access 2000

Problem: Sorting the columns in a graph in ascending order according to the
"read_period" field, not the "Period" field.

Purpose: Show the comparison between the prior year data and the current
year data.

Name read_period Amounts Period
PRIOR ACTUAL 200712 758574 Period 12
PRIOR ACTUAL 200713 788824 Period 13
PRIOR ACTUAL 200801 699768 Period 01
PRIOR ACTUAL 200802 901306 Period 02

ACTUAL 200812 680414 Period 12
ACTUAL 200813 612171 Period 13
ACTUAL 200901 545913 Period 01
ACTUAL 200902 848277 Period 02

PRIOR AVERAGE 200712 770808 Period 12
PRIOR AVERAGE 200713 770808 Period 13
PRIOR AVERAGE 200801 770808 Period 01
PRIOR AVERAGE 200802 770808 Period 02

AVERAGE 200812 767428 Period 12
AVERAGE 200813 767428 Period 13
AVERAGE 200901 767428 Period 01
AVERAGE 200902 767428 Period 02

Here’s the Row Source that I am using:

TRANSFORM Sum(qry_BW_ExpVol_UNION.EXPECT_VOL) AS SumOfEXPECT_VOL

SELECT qry_BW_ExpVol_UNION.Name

FROM qry_BW_ExpVol_UNION

GROUP BY qry_BW_ExpVol_UNION.Organize, qry_BW_ExpVol_UNION.Name

ORDER BY qry_BW_ExpVol_UNION.Organize, "Period " & Right([read_period],2)
PIVOT "Period " & Right([read_period],2);

Result Wanted:

Name Period 12 Period 13 Period 01 Period 02
ACTUAL 680414 612171 545913 848277
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 758574 788824 699768 901306
PRIOR AVERAGE 770808 770808 770808 770808

Actual Result:

Name Period 01 Period 02 Period 12 Period 13
ACTUAL 545913 848277 680414 612171
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 699768 901306 758574 788824
PRIOR AVERAGE 770808 770808 770808 770808

Any suggestions would be appreciated.

Thank you,
Rach
Click to expand...
Click to expand...
Click to expand...
 
Thank you Karl. I'll check that out.

At least in the meantime, I can use what you gave me. It's much appreciated.

-Rach
--
RJF


KARL DEWEY said:
Search on post by Duane Hookum. I think I have seen something on dynamic
crosstab column headings.
--
KARL DEWEY
Build a little - Test a little


RJF said:
Hi Karl,

Thanks so much for the quick response. That works great for this particular
senario. But the Periods could always change on the fly. Sometimes the
underlying query will be Period 12 through Period 02, but it could also be
Period 04 through Period 03. (Period 04 being for 2008 and Period 03 being
for 2009 or for 2007 and 2008) I have a table that holds the Period range
and that is what the underlying query (qry_BW_ExpVol_UNION) is based on.

Is there any way to do this in the correct order if you don't know the range
up front?

Thanks,
Rach


--
RJF


KARL DEWEY said:
Try changing PIVOT statement to read like this --
PIVOT "Period " & Right([read_period],2) In("Period 12", "Period 13",
"Period 01", "Period 02");

--
KARL DEWEY
Build a little - Test a little


:

Access 2000

Problem: Sorting the columns in a graph in ascending order according to the
"read_period" field, not the "Period" field.

Purpose: Show the comparison between the prior year data and the current
year data.

Name read_period Amounts Period
PRIOR ACTUAL 200712 758574 Period 12
PRIOR ACTUAL 200713 788824 Period 13
PRIOR ACTUAL 200801 699768 Period 01
PRIOR ACTUAL 200802 901306 Period 02

ACTUAL 200812 680414 Period 12
ACTUAL 200813 612171 Period 13
ACTUAL 200901 545913 Period 01
ACTUAL 200902 848277 Period 02

PRIOR AVERAGE 200712 770808 Period 12
PRIOR AVERAGE 200713 770808 Period 13
PRIOR AVERAGE 200801 770808 Period 01
PRIOR AVERAGE 200802 770808 Period 02

AVERAGE 200812 767428 Period 12
AVERAGE 200813 767428 Period 13
AVERAGE 200901 767428 Period 01
AVERAGE 200902 767428 Period 02

Here’s the Row Source that I am using:

TRANSFORM Sum(qry_BW_ExpVol_UNION.EXPECT_VOL) AS SumOfEXPECT_VOL

SELECT qry_BW_ExpVol_UNION.Name

FROM qry_BW_ExpVol_UNION

GROUP BY qry_BW_ExpVol_UNION.Organize, qry_BW_ExpVol_UNION.Name

ORDER BY qry_BW_ExpVol_UNION.Organize, "Period " & Right([read_period],2)
PIVOT "Period " & Right([read_period],2);

Result Wanted:

Name Period 12 Period 13 Period 01 Period 02
ACTUAL 680414 612171 545913 848277
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 758574 788824 699768 901306
PRIOR AVERAGE 770808 770808 770808 770808

Actual Result:

Name Period 01 Period 02 Period 12 Period 13
ACTUAL 545913 848277 680414 612171
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 699768 901306 758574 788824
PRIOR AVERAGE 770808 770808 770808 770808

Any suggestions would be appreciated.

Thank you,
Rach
Click to expand...
Click to expand...
Click to expand...
 
I mispelled - it is Duane Hookom.
--
KARL DEWEY
Build a little - Test a little


RJF said:
Thank you Karl. I'll check that out.

At least in the meantime, I can use what you gave me. It's much appreciated.

-Rach
--
RJF


KARL DEWEY said:
Search on post by Duane Hookum. I think I have seen something on dynamic
crosstab column headings.
--
KARL DEWEY
Build a little - Test a little


RJF said:
Hi Karl,

Thanks so much for the quick response. That works great for this particular
senario. But the Periods could always change on the fly. Sometimes the
underlying query will be Period 12 through Period 02, but it could also be
Period 04 through Period 03. (Period 04 being for 2008 and Period 03 being
for 2009 or for 2007 and 2008) I have a table that holds the Period range
and that is what the underlying query (qry_BW_ExpVol_UNION) is based on.

Is there any way to do this in the correct order if you don't know the range
up front?

Thanks,
Rach


--
RJF


:

Try changing PIVOT statement to read like this --
PIVOT "Period " & Right([read_period],2) In("Period 12", "Period 13",
"Period 01", "Period 02");

--
KARL DEWEY
Build a little - Test a little


:

Access 2000

Problem: Sorting the columns in a graph in ascending order according to the
"read_period" field, not the "Period" field.

Purpose: Show the comparison between the prior year data and the current
year data.

Name read_period Amounts Period
PRIOR ACTUAL 200712 758574 Period 12
PRIOR ACTUAL 200713 788824 Period 13
PRIOR ACTUAL 200801 699768 Period 01
PRIOR ACTUAL 200802 901306 Period 02

ACTUAL 200812 680414 Period 12
ACTUAL 200813 612171 Period 13
ACTUAL 200901 545913 Period 01
ACTUAL 200902 848277 Period 02

PRIOR AVERAGE 200712 770808 Period 12
PRIOR AVERAGE 200713 770808 Period 13
PRIOR AVERAGE 200801 770808 Period 01
PRIOR AVERAGE 200802 770808 Period 02

AVERAGE 200812 767428 Period 12
AVERAGE 200813 767428 Period 13
AVERAGE 200901 767428 Period 01
AVERAGE 200902 767428 Period 02

Here’s the Row Source that I am using:

TRANSFORM Sum(qry_BW_ExpVol_UNION.EXPECT_VOL) AS SumOfEXPECT_VOL

SELECT qry_BW_ExpVol_UNION.Name

FROM qry_BW_ExpVol_UNION

GROUP BY qry_BW_ExpVol_UNION.Organize, qry_BW_ExpVol_UNION.Name

ORDER BY qry_BW_ExpVol_UNION.Organize, "Period " & Right([read_period],2)
PIVOT "Period " & Right([read_period],2);

Result Wanted:

Name Period 12 Period 13 Period 01 Period 02
ACTUAL 680414 612171 545913 848277
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 758574 788824 699768 901306
PRIOR AVERAGE 770808 770808 770808 770808

Actual Result:

Name Period 01 Period 02 Period 12 Period 13
ACTUAL 545913 848277 680414 612171
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 699768 901306 758574 788824
PRIOR AVERAGE 770808 770808 770808 770808

Any suggestions would be appreciated.

Thank you,
Rach
Click to expand...
Click to expand...
Click to expand...
 
I do not think so. Like I said, search on post by Duane Hookom - Dynamic
Column Headings.
--
KARL DEWEY
Build a little - Test a little


RJF said:
Hi Karl. One more question...

If I have a table called tlbColumnHeadings containing one field called
Headings, and populate it with the string that I need for the Column Headings
in the graph.

"Period 04","Period 05", "Period 06", "Period 07", "Period 08", "Period
09", "Period 10", "Period 11", "Period 12", "Period 13", "Period 01",
"Period 02"

is there a way to call that string into the Query Properties Column Heading
field?

I tried doing a dlookup and it didn't work. Any ideas on this?

Thanks,


--
RJF


KARL DEWEY said:
I mispelled - it is Duane Hookom.
--
KARL DEWEY
Build a little - Test a little


RJF said:
Thank you Karl. I'll check that out.

At least in the meantime, I can use what you gave me. It's much appreciated.

-Rach
--
RJF


:

Search on post by Duane Hookum. I think I have seen something on dynamic
crosstab column headings.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Thanks so much for the quick response. That works great for this particular
senario. But the Periods could always change on the fly. Sometimes the
underlying query will be Period 12 through Period 02, but it could also be
Period 04 through Period 03. (Period 04 being for 2008 and Period 03 being
for 2009 or for 2007 and 2008) I have a table that holds the Period range
and that is what the underlying query (qry_BW_ExpVol_UNION) is based on.

Is there any way to do this in the correct order if you don't know the range
up front?

Thanks,
Rach


--
RJF


:

Try changing PIVOT statement to read like this --
PIVOT "Period " & Right([read_period],2) In("Period 12", "Period 13",
"Period 01", "Period 02");

--
KARL DEWEY
Build a little - Test a little


:

Access 2000

Problem: Sorting the columns in a graph in ascending order according to the
"read_period" field, not the "Period" field.

Purpose: Show the comparison between the prior year data and the current
year data.

Name read_period Amounts Period
PRIOR ACTUAL 200712 758574 Period 12
PRIOR ACTUAL 200713 788824 Period 13
PRIOR ACTUAL 200801 699768 Period 01
PRIOR ACTUAL 200802 901306 Period 02

ACTUAL 200812 680414 Period 12
ACTUAL 200813 612171 Period 13
ACTUAL 200901 545913 Period 01
ACTUAL 200902 848277 Period 02

PRIOR AVERAGE 200712 770808 Period 12
PRIOR AVERAGE 200713 770808 Period 13
PRIOR AVERAGE 200801 770808 Period 01
PRIOR AVERAGE 200802 770808 Period 02

AVERAGE 200812 767428 Period 12
AVERAGE 200813 767428 Period 13
AVERAGE 200901 767428 Period 01
AVERAGE 200902 767428 Period 02

Here’s the Row Source that I am using:

TRANSFORM Sum(qry_BW_ExpVol_UNION.EXPECT_VOL) AS SumOfEXPECT_VOL

SELECT qry_BW_ExpVol_UNION.Name

FROM qry_BW_ExpVol_UNION

GROUP BY qry_BW_ExpVol_UNION.Organize, qry_BW_ExpVol_UNION.Name

ORDER BY qry_BW_ExpVol_UNION.Organize, "Period " & Right([read_period],2)
PIVOT "Period " & Right([read_period],2);

Result Wanted:

Name Period 12 Period 13 Period 01 Period 02
ACTUAL 680414 612171 545913 848277
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 758574 788824 699768 901306
PRIOR AVERAGE 770808 770808 770808 770808

Actual Result:

Name Period 01 Period 02 Period 12 Period 13
ACTUAL 545913 848277 680414 612171
AVERAGE 767428 767428 767428 767428
PRIOR ACTUAL 699768 901306 758574 788824
PRIOR AVERAGE 770808 770808 770808 770808

Any suggestions would be appreciated.

Thank you,
Rach
Click to expand...
Click to expand...
Click to expand...
 
Back
Top