Custom sort order on chart

[Regan via AccessMonster.com]

Hi i have a chart and i want to be able to change the order on the (y) axis
this is the query it is based on

PARAMETERS [forms]![filters]![txtcboXsecone] Integer, [forms]![filters]!
[txtcboxsectwo] integer;
SELECT pointsdata.NominalChn, pointsdata.Elev, pointsdata.Hal
FROM pointsdata
WHERE pointsdata.NominalChn= [forms]![filters]![txtcboXsecone] Or pointsdata.
NominalChn= [forms]![filters]![txtcboxsectwo];

this is the row source in the chart

TRANSFORM Sum(chndiff.Elev) AS SumOfElev
SELECT chndiff.Hal
FROM chndiff
GROUP BY chndiff.Hal
PIVOT chndiff.NominalChn;

What i want to do is customise the sort order of chndiffHal.

FELN
FLLN
CL1
TDLST1
LLRS
FERS

So my chart looks something like this:

3
2
1
FELN FLLN CL1 TDLST1 LLRS FERS

At the moment it looks like this

3
2
1
CL1 FELN FERS LLLN LLRS TDLST1

Any help would be appreciated, thanks

--
Regan,
Paeroa
World famous in New Zealand

Message posted via AccessMonster.com

 

[Duane Hookom]

Try:

TRANSFORM Sum(chndiff.Elev) AS SumOfElev
SELECT chndiff.Hal
FROM chndiff
GROUP BY chndiff.Hal
PIVOT chndiff.NominalChn IN ("FELN", "FLLN", "CL1", "TDLST1", "LLRS",
"FERS");

 

[Regan via AccessMonster.com]

Try:

TRANSFORM Sum(chndiff.Elev) AS SumOfElev
SELECT chndiff.Hal
FROM chndiff
GROUP BY chndiff.Hal
PIVOT chndiff.NominalChn IN ("FELN", "FLLN", "CL1", "TDLST1", "LLRS",
"FERS");

Hi thanks Duane.

i should have mentioned
its 'chndiff.hal' that needs to be grouped ("FELN", "FLLN", "CL1", "TDLST1",
"LLRS",
"FERS");
Thou i tried what you wrote anyway but it was a no go. I also tried

GROUP BY chndiff.Hal IN ("FELN", "FLLN", "CL1", "TDLST1", "LLRS",
"FERS");

So ill try giving more information

Chart = Line Chart

Chndiff.nominalchn = integer
Chndiff.elev = double
chndiff.Hal = text

Chndiff.Elev on left side
chn.Hal on bottom
and Chndiff.Nominalchn as

The chart is meant to represent a Xsection of a road between 2 Chainages i.e
chn 50 and chn 100
to show the difference in elevation

FELN= Feather edge Left Northbound
LLLN= Landline left Northbound
CL1= Centre line string one
TDLST1= Twin double lines string 1
LLRS=land line right southboud
LLLS=land line left southboud

E
L 3 Line Chn 50
E 2 & Line chn 100
V 1
FELN LLLN CL1 TDLST1 LLRS LLLS

I am really thankful for your help, cheers mate

--
Regan,
Paeroa
World famous in New Zealand

Message posted via AccessMonster.com

 

[Duane Hookom]

All you need to do is add a field to some table containing unique values of
Hal that identifies your sort order. Then include this in your crosstab to
set the order.

 

[Regan via AccessMonster.com]

All you need to do is add a field to some table containing unique values of
Hal that identifies your sort order. Then include this in your crosstab to
set the order.

Cheers Duane, i got it to work

--
Regan,
Paeroa
World famous in New Zealand

Message posted via AccessMonster.com