You are here:

Using MS Access/Sorting Report by Text Box



You have been helping me with a problem, unfortunately I don't see anywhere on our previous discussion a number to reference and it won't let me ask anymore follow up questions. I copied the URL for your response to my last follow up question.

Your last suggestion didn't work. For the dates specified I should have 85 records for the "ACCP" error code and 34 records for the "REPR" error code. Instead when I bring them up together as suggested I get 34 records with both "ACCP" and "REPR" error codes. 53 records with "ACCP" do not show (because they have no "REPR" error code for that fabricator) and 2 records with "REPR" error code do not show (because they have no "ACCP" error code for that fabricator). In this case each record generated by the query is a separate fabricator (with his total number of piece with that error code).

I tried adding the main employees table and linking it to the two queries returning the records, and it still just generates the 32 records mentioned above.

Also, still not getting the formula to work. I can do it with a text box on the report but I like your idea of doing it with the query. I need to reference the field and the table for each query but I don't know how to put the two together. Both queries have the same field name "SumOfQty" but one query is called "qryFabricatorAccpRate2" and one is named "qryFabricatorAccpRate2_1". So do I put it in the field line of the new column as "SumOfQty:qryFabricatorAccpRate2" when I reference each column I want in my math formula, with a colon or some other character between the field and table names?

Sorry for the extended grilling and I appreciate your help.

ANSWER: Yeah that's the one thing I don't like about allexperts is they limit followup.

Can you post the SQL for your query? Open the query in Design view and select SQL view then Copy and paste into a response here.

So the expression should be something like:


If you have fields in a query that have the same name you have to qualify them using the table name.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

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

QUESTION: I have probably done this the hard way but what I did first was create a query that returned all records (all error codes) between given dates. That query is named  "qryFabricatorAccpRate-1"  (the hyphen is in the name) and the SQL is;

SELECT tblERRORS.FabricatorType, tblERRORS.Fabricator, tblERRORS.ErrorCode, tblERRORS.Qty
FROM tblReport INNER JOIN (tblQCPieces INNER JOIN tblERRORS ON tblQCPieces.ReleaseDetailID = tblERRORS.ReleaseDetailID) ON tblReport.ReportNumber = tblQCPieces.ReportNumber
WHERE (((tblERRORS.ErrorCode)="ACCP" Or (tblERRORS.ErrorCode)="REPR") AND ((tblReport.ReportDate) Between [Forms]![frmErrorReport]![txtBeginDate] And [Forms]![frmErrorReport]![txtEndDate]) AND ((tblQCPieces.Green)=Yes))
ORDER BY tblERRORS.FabricatorType, tblERRORS.Fabricator, tblERRORS.ErrorCode;

Then I created the query that would return just the error codes "ACCP" and "REPR" and sum the pieces for each fabricator, that query is named  "qryFabricatorAccpRate2"  (no hyphen in this query name) and the SQL is;

SELECT [qryFabricatorAccpRate-1].Fabricator, [qryFabricatorAccpRate-1].ErrorCode, Sum([qryFabricatorAccpRate-1].Qty) AS SumOfQty
FROM [qryFabricatorAccpRate-1]
GROUP BY [qryFabricatorAccpRate-1].Fabricator, [qryFabricatorAccpRate-1].ErrorCode, [qryFabricatorAccpRate-1].FabricatorType
HAVING ((([qryFabricatorAccpRate-1].ErrorCode)="ACCP" Or ([qryFabricatorAccpRate-1].ErrorCode)="REPR"))
ORDER BY [qryFabricatorAccpRate-1].FabricatorType, [qryFabricatorAccpRate-1].Fabricator, [qryFabricatorAccpRate-1].ErrorCode;

Then I created the query that generates the "ACCP" pieces and "REPR" pieces together and calculates the acceptance rate in the extra column (I got it to work, thank you), this query is named  "qryFabricatorAccpRate-3"  (there is a hyphen in the query name) and the SQL is;

SELECT tblEmployees.Stencil, qryFabricatorAccpRate2.ErrorCode, qryFabricatorAccpRate2.SumOfQty, qryFabricatorAccpRate2_1.ErrorCode, qryFabricatorAccpRate2_1.SumOfQty, [qryFabricatorAccpRate2].[SumOfQty]/([qryFabricatorAccpRate2].[SumOfQty]+[qryFabricatorAccpRate2_1].[SumOfQty]) AS Expr1
FROM (tblEmployees INNER JOIN qryFabricatorAccpRate2 ON tblEmployees.Stencil = qryFabricatorAccpRate2.Fabricator) INNER JOIN qryFabricatorAccpRate2 AS qryFabricatorAccpRate2_1 ON tblEmployees.Stencil = qryFabricatorAccpRate2_1.Fabricator
WHERE (((qryFabricatorAccpRate2.ErrorCode)="accp") AND ((qryFabricatorAccpRate2_1.ErrorCode)="repr"));

It now works as desired with the exception that it only returns the fabricators that have both "ACCP" and "REPR" error codes, if they have only one or the other but not both they do not make it on the report.

The "ACCP" error code is a piece that was fabricated with no mistakes. The "REPR" error code is for pieces that the fabricator made mistakes on. 5 good pieces and 5 bad pieces gives him a 50% acceptance rate.

I appreciate your help and I'm sure that over a month's time everyone will have at least one of each but we do have some good fabricators that might not and we are planning on using this to list the top 25 fabricators and hopefully the guys will work to get the better acceptance rates and get on the list. I will be using this to generate a monthly report and also a three month rolling average and a six month rolling average.

Thank you again,


ANSWER: First, you are doing good. The only change I would make is to eliminate the WHERE clause in the last query. You are already filtering for those in a previous query so its not necessary to do so again.

What I would no try is to change the Join type in the last query. In Query Design mode, right click on the Join line and select Join Properties. Change each Join to show ALL from tblEmployees and Matching from the other query. This will create an Outer Join that may give you all the records you want.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

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


Thank you for the compliment.

When I do as you suggest I get all fabricators returned even the ones without "ACCP" and "REPR" error codes, though only those error codes show up beside the appropriate employees. It also lists all permutations. If A2 has 10 "ACCP" and 5 "REPR" I get four lines, one has 10 ACCP in one column and 10 ACCP in the other. Next line has 10 ACCP in one column and 5 REPR in the other, then 5 REPR in the first col and 10 ACCP in the other and the last line has 5 REPR in one col and 5 REPR in the other col.

The fabricators with just one error code have just the one permutation. B4 has just 3 REPR error codes so he has just one line and it shows as 3 REPR in the first column and 3 REPR in the second column.

When I leave the ACCP and REPR in the criteria part of the last query I get still get only the fabricators that have both ACCP and REPR error codes. I did try experimenting and putting  "ACCP" Or ""  in one column and  "REPR" Or ""  in the other column in the criteria line hoping it would register the missing ACCP (or REPR) as a null but no luck I still get just the fabricators with both error codes.

I appreciate any additional help you can provide.
Thank you,

Hmm, it makes sense that you would get all fabricators, but the multiple lines don't make sense. However, to help further I really need to look at the data.

What I'm going to suggest is you post this at in the Access forum. You can attach a zipped copy of your database and I can take a look and try some things.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




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


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

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

Brooklyn College BA

©2016 All rights reserved.