Sort report based on calculated field 1

[BrockLanders]

Hi, I have an Access report with 4 fields in the detail section: date, qty, amount and % total. The first 3 fields come from a table via a query. The % total field is a calculated field, the formula is qty divided by Sum of qty, which is in the report footer.

Now, I'd like to sort the report based on % total. However, when i go to sorting and grouping in report design view, my calculated field is not listed as an option. I'm guessing because the Sum of qty field is in the footer, but am I missing something. Or is there a workaround for this?

Thanks in advance for any help.

 

[RoyVidar]

Hi!

There might be some workarounds, but I ususally perform the calculations in the query, providing a "valid" field to sort by in the report.

HTH Roy-Vidar

 

[BrockLanders]

Thanks for the reply RoyVidar. What do you mean by a "valid" field in the query.

Thanks

 

[RoyVidar]

hmmmm - not very elegantly put, I fully agree;-(

..."valid" field to sort by in the report

If you perform a calculation in the query, ie:

[tt]MyCalcField: Field1 * Field2[/tt]

or

[tt]MyCalcField: (Select Sum(Field3) as NewSum from tblSomething)[/tt]
...

The MyCalcField shows up as a field to be selected in the report ("valid"/selectable...).

Roy-Vidar

 

[BrockLanders]

Wow, that worked, thanks RoyVidar. I consider myself to be pretty Access savvy, but I never knew how to do running totals in a query. Thanks again!