Overriding Group Sort

C

Chris

I have a report grouped by ID. In one version of this
report, I am hiding the detail section to see only the
subtotal area. I want to sort, not by the ID, but by one
of those subtotals, but the information has to be grouped
by ID in order to have the correct subtotals.

The sorting and grouping button does not allow me to
overrule the sorting by the grouped field. Is there
around this?

I also tried the Order By property, but couldn't get that
to work either.

Any help is appreciated!!!
 
D

Duane Hookom

The only way to group by an expression is to have that expression
"createable" in the report's record source query. For instance, if you want
to sort by SalesPerson and total their sales in the SalesPerson footer and
then Sort/Group By Sum([Sales]), you would need to:
-create a totals query that sums sales by SalesPerson
-add this query to the report's record source query
-join the SalesPerson fields
-add the SumOfSales to the query grid
-Set the primary sorting to SumOfSales
-set the secondary sorting to SalesPerson
 
C

Chris

Thanks, I was hoping to use the existing report without
having to create a new one, hide the detail and just
change the sorting.
-----Original Message-----
The only way to group by an expression is to have that expression
"createable" in the report's record source query. For instance, if you want
to sort by SalesPerson and total their sales in the SalesPerson footer and
then Sort/Group By Sum([Sales]), you would need to:
-create a totals query that sums sales by SalesPerson
-add this query to the report's record source query
-join the SalesPerson fields
-add the SumOfSales to the query grid
-Set the primary sorting to SumOfSales
-set the secondary sorting to SalesPerson

--
Duane Hookom
MS Access MVP


I have a report grouped by ID. In one version of this
report, I am hiding the detail section to see only the
subtotal area. I want to sort, not by the ID, but by one
of those subtotals, but the information has to be grouped
by ID in order to have the correct subtotals.

The sorting and grouping button does not allow me to
overrule the sorting by the grouped field. Is there
around this?

I also tried the Order By property, but couldn't get that
to work either.

Any help is appreciated!!!
Click to expand...


.
Click to expand...
 
D

Duane Hookom

I didn't suggest anything about creating a new report. All I suggested was a
method to include aggregate totals in your current report's record source.

--
Duane Hookom
MS Access MVP
--

Chris said:
Thanks, I was hoping to use the existing report without
having to create a new one, hide the detail and just
change the sorting.
-----Original Message-----
The only way to group by an expression is to have that expression
"createable" in the report's record source query. For instance, if you want
to sort by SalesPerson and total their sales in the SalesPerson footer and
then Sort/Group By Sum([Sales]), you would need to:
-create a totals query that sums sales by SalesPerson
-add this query to the report's record source query
-join the SalesPerson fields
-add the SumOfSales to the query grid
-Set the primary sorting to SumOfSales
-set the secondary sorting to SalesPerson

--
Duane Hookom
MS Access MVP


I have a report grouped by ID. In one version of this
report, I am hiding the detail section to see only the
subtotal area. I want to sort, not by the ID, but by one
of those subtotals, but the information has to be grouped
by ID in order to have the correct subtotals.

The sorting and grouping button does not allow me to
overrule the sorting by the grouped field. Is there
around this?

I also tried the Order By property, but couldn't get that
to work either.

Any help is appreciated!!!
Click to expand...


.
Click to expand...
Click to expand...
 
Top