You are here:

Using MS Access/Sorting Report by Text box

Advertisement


Question
QUESTION: Scott,

I have a report that lists Fabricators and the number of pieces fabricated correctly and incorrectly. The report returns each fabricator in a field control called "Fabricator" (the only field control in this part of the report) then returns the number of pieces fabricated correctly in a text box named Text31 with the control source being =Sum(IIf([ErrorCode]="ACCP",[Qty],0)), the number of pieces fabricated incorrectly in a text box named Text37 with the control source being =Sum(IIf([ErrorCode]="REPR",[Qty],0)) then in a fourth control a text box named Text39 presents an acceptance rate with the control source being =[text31]/([text31]+[text37]) and set to percent with one decimal place. This shows on the report as four controls side by side and a separate line for each fabricator. The report is grouped by "Fabricator" so the report sorts by "Fabricator" (the first control) but I would actually like to order these by the best acceptance rate (the last control) (100.0%) to the worst (48.9%).

I have tried a number of ways, including "Sorting & Grouping" (placing Text39 in Field/Expression), and have looked in the Answers on the Experts Access but have had no success in working this out, any suggestions?

Thank you in advance,
Ed

ANSWER: First I would do most of this in a query rather than on the report.

I would have a query that groups by Fabricator then Error Code, so you get a sum of the quantity for each Error Code. I would then create a query that joins this query and and alias on Fabricator and lists the two sums in separate columns. Adding a column to calculate the acceptance rate.

Then create your report sorting on Acceptance rate.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott,

I'm afraid I'm a little lost.

I've generated the query that shows records for the two error codes I'm wanting to calculate on a report. (ten records with the ACCP error code and 5 records with the REPR error code for a particular Fabricator, other fabricators follow) I don't know how to have a query group and can't find a group option in the various menus nor how to show these totals in the query, 10 and 5 for a fabricator.

Next issue is how to join that query to an alias. I'm not familiar with the usage of that term in this context. Then how to do the percent calculation in the new column?

Thank you in advance,

Ed

ANSWER: Sorry, I don't know what your knowledge level is so I start off with an outline of what should be done hoping you will followup with specific questions if needed.

So, first the Group By. You change a query to a Group By by pressing the Sigma icon when in Query Design mode. This adds a Group By Row to your query. You set the Fabricator and Error Code columns to Group By and Qty column to Sum. So this will give total quantity for each Error code for each Fabricator. If you have more than 2 Error Codes but only want to see the 2, then set the Criteria in that column to filter for the 2 codes.

Next open Query Design mode and add that query TWICE. This will create two entries int he table pane for Query1 and Query1_A (or whatever you named the query).

Join them on Fabricator and add the fabricator and the 2 Sum columns to your query. Finally add a column to the query for the Acceptance rate using the same expression but with the column names. For example:

AcceptanceRate: SumofQtyACCP/SumOfQtyAccp+SumofQtyREPR

Please feel free to ask more followups if you need to.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott,

No apology necessary, you've helped me before and probably thought I knew more than I did. I'm not too bad with some parts but not so good with others.

I have it working to a degree. If a fabricator has both "ACCP" and "REPR" error codes he shows up with his quantities for "ACCP" and "REPR" but if he only has "ACCP" and no "REPR" he doesn't show up at all (and the same if the inverse is true). How can I get fabricator "A2" to show up with his 3 pieces that are "ACCP" when he has no pieces that are "REPR"?

Next, in getting the acceptance rate to calculate in the query, where does the formula go (the "Field" line, "Table" line or "Criteria" line)? When I try to put a name in the "Field" line of the new column it puts  Expr1:  in front of the name and puts brackets around the name. When I try putting the formula there the query doesn't work.

Thank you in advance,

Ed

Answer
Ok, The reason why a fabricator is not showing up is because you are looking for records where there is a match between the query and the alias query. I hadn't considered that issue so it makes your query a bit more complex. I'm going to assume you have a table of Fabricators, if you don't you should. So what you should do is add that table to your query. Then join each of the 2 versions of the query on Fabricator. So you have one join going from the fabricator table to the Fabricator field in each of the 2 representations of the query. Then pull the Fabricator info from the Fabricator table. That should solve that problem.

The expression should go in the Field line. Whenever you have a calculated column in a query you have to give it an alias. If you don't Access will supply the alias as Expr1: (or whatever number). That's why I wrote the expression as AcceptanceRate: . So that would be the alias.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.