Query limit? Getting a little overwhlemed.

[Alhassani]

The query is working in the sample database. When I try to switch to design view this is the error message I get "Microsoft Access can’t represent the join expression Archive_AuditDb.[PCH: Sponsor SSN] = Audit_Defect_Codes_Audit_1.AuditCode in Design view." I keep checking over it, but everything appears to be spelled correctly.

[rpeare]

you're linking the field [PCH: Sponsor SSN] on the table Archive_AuditDB to the field AuditCode in the table Audit_Defect_Codes_Audit_1. Are you sure this link is correct, look at the join linking your ARCHIVE_AUDITDB to AUDIT_DEFECT_CODES_AUDIT_1 I suspect one of those fields either doesn't exist or isn't spelled correctly.

[Alhassani]

Ok, I have toned down this query to only pull 1 field for now. Once this one field is solved, I'll add in all the rest.

SELECT Archive_AuditDb.Week, Archive_AuditDb.RMs, SUM(iif(Audit_Defect_Codes_Audit1)![passemployee]=-1,1,0)/COUNT([DCN]) AS Audit1pct
FROM (Archive_AuditDb LEFT JOIN Audit_Defect_Codes AS Audit_Defect_Codes.Audit1 ON Archive_AuditDb.[PCH: Sponsor SSN] = Audit_Defect_Code_Audit1.Auditcode)
GROUP BY Archive_AuditDb.Week, Archive_AuditDb.RMs;

On the final pieces of both the SELECT and FROM clauses, the links are different. The SELECT clause creates "Audit1pct" while the FROM clause creates "Auditcode". These are both created into the Audit1 table. Since this is the temptable being created by the query, I cannot look at the structure of the table for editing. I'm not sure if this is where the error is. The code above tells me that the error is in the FROM clause. I have tried making "Audit1pct" the reference for both the SELECT and FROM clauses. It didn't work.

This is linked almost exactly as you built it originally. I changed only what needed to be which was tblcodes to Audit_Defect_Codes and Audit_Field_1 to PCH: Sponsor SSN. Banging my head against a wall here. I'm starting to think this is coming down to missing parenthesis.

[rpeare]

why are you creating a temp table? the queries I gave you do not include a temp table. Just your raw data and a link to a table that includes your codes (which you said you already had). There should be no need to include a temp table at all and without seeing your exact tables (structure) I don't know that I can do any better than I have. All three of my queries are single queries with no temp tables.

[Alhassani]

I interpreted "Audit_Defect_Code_Audit1" as a temp table. Is this incorrect?

If so, then that may be the problem.

[Alhassani]

Turned out to be typos all around. Formula works great now in the main database. The typos are bolded below.

SELECT Archive_AuditDb.Week, Archive_AuditDb.RMs, SUM(IIf([Audit_Defect_Codes_Audit1]![passemployee]=-1,1,0))/Count([dcn]) AS Audit1Pct
FROM (Archive_AuditDb LEFT JOIN Audit_Defect_Codes AS Audit_Defect_Codes_Audit1 ON Archive_AuditDb.[PCH: Sponsor SSN] = Audit_Defect_Codes_Audit1.defectcode)
GROUP BY Archive_AuditDb.Week, Archive_AuditDb.RMs;

[rpeare]

So you problem is solved then?

[Alhassani]

Yes, I finished up all of the employee queries last night and am working on the project queries now. I have ran into one thing that I can't explain for the life of me though.

I have one query that works perfectly fine, no popups when ran. When I copy that query and run, it pops up and asks for the field value. But, I didn't change anything. It is the exact same SQL statement down to the digit. I can work around it, but it is a little bit a of head scratcher.