Sort by value, not alpha

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StephanieH - 07 Dec 2005 13:58 GMT

This is one of my first reports I've put together, so bear with me if I'm not
providing the information you need.

I've created a report rtpInternaltoPrimary based directly off the table
tblInternaltoPrimary. In the report, I've Grouped by the field "Summary".
In the Group footer, I've added a calculated field =Sum([Amt]) which gives me
the total for each type of summary. I would like for the report to sort
based on the total amount for each summary type, not in alpha order.

Reply to this Message

Marshall Barton - 07 Dec 2005 20:27 GMT

>This is one of my first reports I've put together, so bear with me if I'm not
>providing the information you need.
[quoted text clipped - 4 lines]
>the total for each type of summary. I would like for the report to sort
>based on the total amount for each summary type, not in alpha order.

For you to tell the report to sort on a total, the value of
the total must be in the report's record source query.

You will need a Totals type query to calculate the sum for
each summary.
qryTotal:

SELECT Summary, Sum(Amt) As GrpAmt
FROM tblInternaltoPrimary
GROUP BY Summary

Then join that query to the table (on the summary field) in
another query that you use as the report's record source.

SELECT tblInternaltoPrimary.*, qryTotal.GrpAmt
FROM tblInternaltoPrimary.Summary
INNER JOIN qryTotal
ON tblInternaltoPrimary.Summary = qryTotal.GrpAmt

Signature

Marsh
MVP [MS Access]

Reply to this Message

StephanieH - 12 Dec 2005 17:46 GMT

Works perfectly.

Thanks Marsh

> >This is one of my first reports I've put together, so bear with me if I'm not
> >providing the information you need.
[quoted text clipped - 23 lines]
> INNER JOIN qryTotal
> ON tblInternaltoPrimary.Summary = qryTotal.GrpAmt

Reply to this Message