How to Sort by a text box on a report

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?
thandelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
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.
0
thandelAuthor Commented:
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?
0
lluddenCommented:
You can do an order by in the query.

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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:

;-)

JeffCoachman
db3.mdb
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.