Custom sort in graphs...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tara - 30 Nov 2005 16:02 GMT

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.

Reply to this Message

Duane Hookom - 30 Nov 2005 17:21 GMT

Share your Row Source SQL View.

Signature

Duane Hookom
MS Access MVP
--

>I know I must be missing something, but for the life of me I cannot seem
>get
[quoted text clipped - 13 lines]
> change
> the data labels. What am I doing wrong?? Any help is appreciated.

Reply to this Message

Tara - 01 Dec 2005 15:46 GMT

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;

> Share your Row Source SQL View.
>
[quoted text clipped - 15 lines]
> > change
> > the data labels. What am I doing wrong?? Any help is appreciated.

Reply to this Message

Duane Hookom - 01 Dec 2005 16:32 GMT

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;

Signature

Duane Hookom
MS Access MVP
--

> Thanks for the response Duane. Here's the underlying query in SQL view,
> and
[quoted text clipped - 49 lines]
>> > change
>> > the data labels. What am I doing wrong?? Any help is appreciated.

Reply to this Message

Tara - 01 Dec 2005 18:38 GMT

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.

> Do yourself a favor and add a field or make a table so you can store the
> sorting values...
[quoted text clipped - 67 lines]
> >> > change
> >> > the data labels. What am I doing wrong?? Any help is appreciated.

Reply to this Message

Ron B - 01 Dec 2005 01:29 GMT

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)

Signature

Ron

> 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
[quoted text clipped - 7 lines]
> 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.

Reply to this Message

Tara - 01 Dec 2005 15:48 GMT

Thanks for the reply Ron. Unfortunately, the sorting doesn't carry over from
the query to the report.

> Tara
>
[quoted text clipped - 16 lines]
> > 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.

Reply to this Message