Sorting based on an expression

[HeatherD25]

Hi,

I have a report set up where I've set the grouping and sorting to:

Team Name
Title
Full Name

Basically, sorts the report by team, then puts people with the same titles
together in alphabetical order. I would like to now keep the same idea, but
now sort the people with the same titles in descending order based on the #
of hours they've worked.

I only show the footer of "Full Name" because I have it sum up data for a
period of time and I don't want it to show the individual monthly data. So
the value I want to sort is an expression called [Text152] -- it's calculated
when the report is opened. How do I sort based on an expression that is
calculated when the report is opened?

Thanks!!
Heather

 

[Duane Hookom]

You can't sort a report based on a value in a control. You can sort a report
based on a value that can be calculated from a single record in the report's
record source.

You should be able to create a totals query tha calculates the "sort"
expression. Then add this query to the report's record source query joining
the appropriate fields. This allows you to use the calculated value in the
sorting and grouping.

 

[HeatherD25]

At the risk of asking a dumb question, how do I use two different queries in
one report? I know that I can generally combine two queries into one query,
but I don't know how I would do that when one query is details and the other
query is a sum of those details. Any ideas for me?

Thanks again!!!

You can't sort a report based on a value in a control. You can sort a report
based on a value that can be calculated from a single record in the report's
record source.

You should be able to create a totals query tha calculates the "sort"
expression. Then add this query to the report's record source query joining
the appropriate fields. This allows you to use the calculated value in the
sorting and grouping.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Hi,

I have a report set up where I've set the grouping and sorting to:

Team Name
Title
Full Name

Basically, sorts the report by team, then puts people with the same titles
together in alphabetical order. I would like to now keep the same idea, but
now sort the people with the same titles in descending order based on the #
of hours they've worked.

I only show the footer of "Full Name" because I have it sum up data for a
period of time and I don't want it to show the individual monthly data. So
the value I want to sort is an expression called [Text152] -- it's calculated
when the report is opened. How do I sort based on an expression that is
calculated when the report is opened?

Thanks!!
Heather

 

[Duane Hookom]

"how I would do that when one query is details and the other query is a sum
of those details"

Your summary query would be grouped by some field or fields that are also in
your report's query. Just add the totals query in with your report's query
and join these fields. This shouldn't increase the total number of records
returned by the query if done correctly.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

At the risk of asking a dumb question, how do I use two different queries in
one report? I know that I can generally combine two queries into one query,
but I don't know how I would do that when one query is details and the other
query is a sum of those details. Any ideas for me?

Thanks again!!!

You can't sort a report based on a value in a control. You can sort a report
based on a value that can be calculated from a single record in the report's
record source.

You should be able to create a totals query tha calculates the "sort"
expression. Then add this query to the report's record source query joining
the appropriate fields. This allows you to use the calculated value in the
sorting and grouping.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Hi,

I have a report set up where I've set the grouping and sorting to:

Team Name
Title
Full Name

Basically, sorts the report by team, then puts people with the same titles
together in alphabetical order. I would like to now keep the same idea, but
now sort the people with the same titles in descending order based on the #
of hours they've worked.

I only show the footer of "Full Name" because I have it sum up data for a
period of time and I don't want it to show the individual monthly data. So
the value I want to sort is an expression called [Text152] -- it's calculated
when the report is opened. How do I sort based on an expression that is
calculated when the report is opened?

Thanks!!
Heather