Sort by value, not alpha

[Guest]

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.

 

[Marshall Barton]

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.

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

 

[Guest]

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.

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.

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