Sort by value, not alpha

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
StephanieH said:
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.
Click to expand...


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
 
Works perfectly.

Thanks Marsh

Marshall Barton said:
StephanieH said:
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.
Click to expand...


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
Click to expand...
 
Back
Top