Sorting in a report

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

Guest

I have a report set up that only shows subtotals, no detail. I would like to
sort by the subtotal. I tried putting a calculation in the field/expression
section of the sorting and grouping box but that didn't work. Any thoughts?
 
Joanne,

You will need to change the design of your report. You will need to
construct a query that returns your sub-totals, and then base your
report on that query, so that the sub-total figures end up in the
report's Detail section. Then you will be able to order them accordingly.
 
Thanks I'll try that. Makes sense

Steve Schapel said:
Joanne,

You will need to change the design of your report. You will need to
construct a query that returns your sub-totals, and then base your
report on that query, so that the sub-total figures end up in the
report's Detail section. Then you will be able to order them accordingly.
Click to expand...
 
Follow up question: I did as suggested and it worked great. Now I added a
calculated control and want to sort by that. I tried 2 different ways and
neither worked. First I put the calculation in the control on the report and
in the sorting box. When I tried to run the report I got a message
"overflow", whatever that means. Then I tried creating an expression in the
query. The query actually ran fine, but the report became a total mess. The
fields used in the expression no longer even appeared on the report nor did
the expression. I've written many expressions before with no problem, but
never on a sum. Is there a different method when the fields used in the
expression are sums?
 
Joanne,

Either approach should work - as long as you get the expression right :-)

I can't really comment more explicitly, without knowing the details of
your data, and seeing examples of your figures. Can you explain what
this calculation is all about?
 
I'll try to give details an concisely as possible. It's a selling report and
the calculation I'm doing is % of good sold. The calculation is units
sold/(units sold +units in stock). Simple. When I put that as a calculation
in the control of the report it works fine. I think I must be writing the
calculation wrong in the sorting box. I am copying it exactly as it is in
the control and make it the first level of sorting but it doesn't work. I
get the overflow message. I'm putting the name of each component in []. The
exact formula is =[Sumofunits ytd]/([Sumofunits ytd]+[Sumofunits oh]) Is
there a different format I should be using for the expression? I haven't done
too much sorting on expressions so I'm not sure of the format.
 
Joanne,

Ok, what you have done seems correct and logical. But it appears that
your report is based on a Totals query, and therefore the expression you
are using is using values that are the result of aggregate functions
(e.g. Sum). Which is a problem. My suggestion for the next thing to
try... Make another query, based on your existing query, and then put a
calculated field in there...
Good Sold: [Sumofunits ytd]/([Sumofunits ytd]+[Sumofunits oh])
Then base your report on this second level query, and use the [Good
Sold] for sorting.
 
Steve,
Thanks for your help. I will try this and let you know. Much appreciated.

Joanne

Steve Schapel said:
Joanne,

Ok, what you have done seems correct and logical. But it appears that
your report is based on a Totals query, and therefore the expression you
are using is using values that are the result of aggregate functions
(e.g. Sum). Which is a problem. My suggestion for the next thing to
try... Make another query, based on your existing query, and then put a
calculated field in there...
Good Sold: [Sumofunits ytd]/([Sumofunits ytd]+[Sumofunits oh])
Then base your report on this second level query, and use the [Good
Sold] for sorting.

--
Steve Schapel, Microsoft Access MVP
I'll try to give details an concisely as possible. It's a selling report and
the calculation I'm doing is % of good sold. The calculation is units
sold/(units sold +units in stock). Simple. When I put that as a calculation
in the control of the report it works fine. I think I must be writing the
calculation wrong in the sorting box. I am copying it exactly as it is in
the control and make it the first level of sorting but it doesn't work. I
get the overflow message. I'm putting the name of each component in []. The
exact formula is =[Sumofunits ytd]/([Sumofunits ytd]+[Sumofunits oh]) Is
there a different format I should be using for the expression? I haven't done
too much sorting on expressions so I'm not sure of the format.
Click to expand...
Click to expand...
 
Steve,
It worked!! Thanks so much for your help.

Joanne

Joanne said:
Steve,
Thanks for your help. I will try this and let you know. Much appreciated.

Joanne

Steve Schapel said:
Joanne,

Ok, what you have done seems correct and logical. But it appears that
your report is based on a Totals query, and therefore the expression you
are using is using values that are the result of aggregate functions
(e.g. Sum). Which is a problem. My suggestion for the next thing to
try... Make another query, based on your existing query, and then put a
calculated field in there...
Good Sold: [Sumofunits ytd]/([Sumofunits ytd]+[Sumofunits oh])
Then base your report on this second level query, and use the [Good
Sold] for sorting.

--
Steve Schapel, Microsoft Access MVP
I'll try to give details an concisely as possible. It's a selling report and
the calculation I'm doing is % of good sold. The calculation is units
sold/(units sold +units in stock). Simple. When I put that as a calculation
in the control of the report it works fine. I think I must be writing the
calculation wrong in the sorting box. I am copying it exactly as it is in
the control and make it the first level of sorting but it doesn't work. I
get the overflow message. I'm putting the name of each component in []. The
exact formula is =[Sumofunits ytd]/([Sumofunits ytd]+[Sumofunits oh]) Is
there a different format I should be using for the expression? I haven't done
too much sorting on expressions so I'm not sure of the format.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top