How to Sort by a text box on a report

Posted on 2013-10-10
Medium Priority
Last Modified: 2013-10-10
I have a report that looks through a single field in a table.  I am trying to have the report group and tally up all simliar products and provide a percentage for each item based on the total items in the report.

I have it working but grouping on the product, and having a text box dividing the toal group cound (grouping by item) by the count(*) of all items in the report which is in the foter of the report amount to get a percent. =Format(Count(*)/[TotalCount],"00.0%")

Only issue is that I would liek to sort by this item percent text box value so I can see largest to least products counts in the table.

How can I sort the report based on the text box item percentage or should I be using another method?
Question by:thandel
  • 2
LVL 18

Expert Comment

ID: 39564067
If each row is a single row in the query, and there are not any subgroupings, ordering by the quantity will give the same result as ordering by the %

If there are 4 items in the report, with quantities 1, 3, 4, 2 the percentages are 10, 30, 40, 20 - the same as the quantity.

If you need to do more calculations, then you can make a sub query that brings back the total, then join it to the original query and do the calculations in the query.

Author Comment

ID: 39564079
Ok I think I understand but how do I orde by the Qty (or other field for that matter)  in the grouping options its only allowing me to select items from the table not items in the report.

Do I need a separate query or queries?
LVL 18

Accepted Solution

lludden earned 1000 total points
ID: 39564103
You can do an order by in the query.

SELECT ItemGroup, Count(*) AS Qty
GROUP BY ItemGroup
ORDER BY Count(*)

Open in new window

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 total points
ID: 39564450
This one of the reasons why you should include your calculations in the Reports Recordsource, and not do the calculations in a control...
If you do the calculations in a the recordsource, this will be a field, just like all the others, then you can just select that field to sort the report on...

It is not clear if you have only the one textbox in the footer.
It is not clear how you are grouping...
This is why it is always best to simply post a sample of what you have, and a clear graphical example of exactly what you want.

In any event, this sample illustrates the technique:



Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question