Custom sort in graphs...

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

Guest

I know I must be missing something, but for the life of me I cannot seem get
the graph in a report I'm working on to sort the way I want it to! I've
tried everything I can think of, but I'm stuck. For example, I'm working on
a very simple report that shows the education levels of the participants in
one of my agency's programs. I want it to go from lowest level to highest
level. Getting it to sort the way I want it to in the underlying query is no
problem. I just used an expression to define the custom sort order
numerically. When I do it this way, the graph itself looks just the way I
wanted it too. However, the data labels show up as the numerical equivalent
that I created in the underlying query. For whatever reason, I can't change
the data labels. What am I doing wrong?? Any help is appreciated.
 
Tara

If I have uderstood the problem correctly you can graph the data using the
fields that you want as data lables and have the sort as an extra column in
the query with the "show" field unchecked. I usually put the sort field at
the end as it sometimes gets inconsistent unless you get into the SQL and
access tends to interpret things from left to right (as in caluclated fields
etc)
 
Thanks for the response Duane. Here's the underlying query in SQL view, and
following that is the row source for the report in SQL view.

QuerySQL:

SELECT [TblDemographics].[MotherEducation],
Count([TblDemographics].[MotherEducation]) AS CountOfMotherEducation
FROM TblDemographics
GROUP BY [TblDemographics].[MotherEducation], IIf([MotherEducation]="8th
Grade or Less",1,IIf([MotherEducation]="sp ed --1 yr high
school",2,IIf([MotherEducation]="Some High
School",3,IIf([MotherEducation]="High School
Grad/GED",4,IIf([MotherEducation]="Some College",5)))))
ORDER BY IIf([MotherEducation]="8th Grade or
Less",1,IIf([MotherEducation]="sp ed --1 yr high
school",2,IIf([MotherEducation]="Some High
School",3,IIf([MotherEducation]="High School
Grad/GED",4,IIf([MotherEducation]="Some College",5)))));

Row Source SQL view:

SELECT QryAggregateEducation.MotherEducation,
Sum(QryAggregateEducation.CountOfMotherEducation) AS
SumOfCountOfMotherEducation
FROM QryAggregateEducation
GROUP BY QryAggregateEducation.MotherEducation;
 
Thanks for the reply Ron. Unfortunately, the sorting doesn't carry over from
the query to the report.
 
Do yourself a favor and add a field or make a table so you can store the
sorting values...

MotherEducation SortVal
8th Grade or Less 1
sp ed --1 yr high school 2
Some High School 3

Then add the SortVal column to your first query and group by it so you can
get rid of those horrible nested IIf()s.
Use the SortVal in your Row Source like:
SELECT QryAggregateEducation.MotherEducation,
Sum(QryAggregateEducation.CountOfMotherEducation) AS
SumOfCountOfMotherEducation
FROM QryAggregateEducation
GROUP BY SortVal, QryAggregateEducation.MotherEducation
ORDER BY SortVal, MotherEducation;


--
Duane Hookom
MS Access MVP
--

Tara said:
Thanks for the response Duane. Here's the underlying query in SQL view,
and
following that is the row source for the report in SQL view.

QuerySQL:

SELECT [TblDemographics].[MotherEducation],
Count([TblDemographics].[MotherEducation]) AS CountOfMotherEducation
FROM TblDemographics
GROUP BY [TblDemographics].[MotherEducation], IIf([MotherEducation]="8th
Grade or Less",1,IIf([MotherEducation]="sp ed --1 yr high
school",2,IIf([MotherEducation]="Some High
School",3,IIf([MotherEducation]="High School
Grad/GED",4,IIf([MotherEducation]="Some College",5)))))
ORDER BY IIf([MotherEducation]="8th Grade or
Less",1,IIf([MotherEducation]="sp ed --1 yr high
school",2,IIf([MotherEducation]="Some High
School",3,IIf([MotherEducation]="High School
Grad/GED",4,IIf([MotherEducation]="Some College",5)))));

Row Source SQL view:

SELECT QryAggregateEducation.MotherEducation,
Sum(QryAggregateEducation.CountOfMotherEducation) AS
SumOfCountOfMotherEducation
FROM QryAggregateEducation
GROUP BY QryAggregateEducation.MotherEducation;



Duane Hookom said:
Share your Row Source SQL View.
Click to expand...
Click to expand...
 
Thanks Duane! It worked. I had played around with the Row Source before I
posted but still couldn't get it. I don't think I tried putting it into the
GROUP BY statement though, only the ORDER BY.

Duane Hookom said:
Do yourself a favor and add a field or make a table so you can store the
sorting values...

MotherEducation SortVal
8th Grade or Less 1
sp ed --1 yr high school 2
Some High School 3

Then add the SortVal column to your first query and group by it so you can
get rid of those horrible nested IIf()s.
Use the SortVal in your Row Source like:
SELECT QryAggregateEducation.MotherEducation,
Sum(QryAggregateEducation.CountOfMotherEducation) AS
SumOfCountOfMotherEducation
FROM QryAggregateEducation
GROUP BY SortVal, QryAggregateEducation.MotherEducation
ORDER BY SortVal, MotherEducation;


--
Duane Hookom
MS Access MVP
--

Tara said:
Thanks for the response Duane. Here's the underlying query in SQL view,
and
following that is the row source for the report in SQL view.

QuerySQL:

SELECT [TblDemographics].[MotherEducation],
Count([TblDemographics].[MotherEducation]) AS CountOfMotherEducation
FROM TblDemographics
GROUP BY [TblDemographics].[MotherEducation], IIf([MotherEducation]="8th
Grade or Less",1,IIf([MotherEducation]="sp ed --1 yr high
school",2,IIf([MotherEducation]="Some High
School",3,IIf([MotherEducation]="High School
Grad/GED",4,IIf([MotherEducation]="Some College",5)))))
ORDER BY IIf([MotherEducation]="8th Grade or
Less",1,IIf([MotherEducation]="sp ed --1 yr high
school",2,IIf([MotherEducation]="Some High
School",3,IIf([MotherEducation]="High School
Grad/GED",4,IIf([MotherEducation]="Some College",5)))));

Row Source SQL view:

SELECT QryAggregateEducation.MotherEducation,
Sum(QryAggregateEducation.CountOfMotherEducation) AS
SumOfCountOfMotherEducation
FROM QryAggregateEducation
GROUP BY QryAggregateEducation.MotherEducation;



Duane Hookom said:
Share your Row Source SQL View.

--
Duane Hookom
MS Access MVP
--

I know I must be missing something, but for the life of me I cannot seem
get
the graph in a report I'm working on to sort the way I want it to!
I've
tried everything I can think of, but I'm stuck. For example, I'm
working
on
a very simple report that shows the education levels of the
participants
in
one of my agency's programs. I want it to go from lowest level to
highest
level. Getting it to sort the way I want it to in the underlying query
is
no
problem. I just used an expression to define the custom sort order
numerically. When I do it this way, the graph itself looks just the
way I
wanted it too. However, the data labels show up as the numerical
equivalent
that I created in the underlying query. For whatever reason, I can't
change
the data labels. What am I doing wrong?? Any help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top