Sorting in a report

[Joanne]

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?

 

[Steve Schapel]

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.

 

[Joanne]

Thanks I'll try that. Makes sense

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.

 

[Joanne]

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?

 

[Steve Schapel]

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?

 

[Joanne]

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.

 

[Steve Schapel]

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.

 

[Joanne]

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

Joanne

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.

 

[Joanne]

Steve,
It worked!! Thanks so much for your help.

Joanne

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

Joanne

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.