Aggregate function to sort of report

[Drew]

I discovered that a report of ours was sorted on a specific
field. This caused a problem because the field it should
be reporting on is actually the result of an aggregate
function. Problem is that Access does not allow an
aggregate function in Sorting and Grouping.

Anyone know of a way, outside of coding it in VB (would
prefer to avoid this, because as far as I can tell that
woulsd mean putting to create all of the fields, etc.)
since there really is no VB code in the report now.

Thanks!

 

[Duane Hookom]

Can you replace the aggregate function with a domain aggregate function like
DCount() or DSum()?

 

[Drew]

I'm familiar with using those functions in Excel, but I
don't see how they would work in an Access report. Fields
from a query are being added in the aggregate function.

 

[Duane Hookom]

Please provide your report recordsource SQL and the fields/expressions that
are used to sort/group in the report.

 

[Drew]

This is the calc that is used for field text0

=Sum([SumOfAWARDED_TOTAL])+nz
([SumOfSumOfCHANGE_ORDER_PRICE],0)

The sum that is calculated is what the report needs to be
sorted on.

Thanks!

 

[Drew]

I fixed the problem. I put in an expression in the report
query to perform the aggregate function and then put the
expression name in the sorting and grouping property.

Thanks for looking into it Duane.

-----Original Message-----
This is the calc that is used for field text0

=Sum([SumOfAWARDED_TOTAL])+nz
([SumOfSumOfCHANGE_ORDER_PRICE],0)

The sum that is calculated is what the report needs to be
sorted on.

Thanks!

-----Original Message-----
Please provide your report recordsource SQL and the fields/expressions that
are used to sort/group in the report.

.