John,
I thought of that, however, when there are 15 different pass or fail fields, I would have to put <> under each field which would make 15 rows of criteria at the bottom of the query. I was trying to come up with a calculation that would add all the results and then use that result of my criteria.
Thats why I tried the: PassTotal: Iif((PassAgree + FailAgree+ Pass2Agree + Fail2Agree) <> 0, "X") expression where I would grab any record with the "X" in the field. However, it didn't work as since they are calculated fields, I get an error when I run the query.
John F.
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> John-
>
> Why are you using an expression at all? Under the PassMRR field, put:
>
> <>[PassIRR]
>
> .. and be done with it!
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
> Sent: Friday, August 17, 2012 4:54 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Counting number of expressions that are <> 0
>
> I am working with the query(s) from hell that I can't modify (using MS Access
> 2003). There are 12 different queries, each with different "Pass" "Fail" fields
> (there can be as many as 15 different Pass fail fields).
>
> 1. The first user goes through the records (using a form) and marks Pass or
> Fail on each field.
>
> 2. The second user does a quality check of a random sample of the same records,
> and marks Pass or Fail on the same fields (different field names of course).
>
> 3. The final step is a final reviewer looks at any record where Reviewer ones
> pass-fail checks don't match Reviewer twos checks.
>
> So, now I am tasked with coming up with a query to figure out how to select
> records where these pass and fail fields don't match. Each Pass Fail field has
> an expression (such as PassAgree: iif([PassMRR]<>[PassIRR], 1, 0)) to evaluate
> if the first review (MRR) matches the second review (IRR). If they don't match,
> then it returns a "1".
>
> Next, I came up with a simple expression to sum all the expressions that <> 0:
> PassTotal: Iif((PassAgree + FailAgree+ Pass2Agree + Fail2Agree) <> 0, "X")
>
> This query works and puts an "X" in any record that <> 0. Now the problem is,
> when I try to put "X" as slection criteria, since PassAgree is a calculated
> field, Access prompts me to enter each field (PassAgree, FailAgree etc.).
>
> I need help figuring out how to evaluate each record where the two reviews
> differ, and select only those records.
>
> I'm afraid their might be propriatary info so I can't include the SQL code.
>
> Any ideas?
>
> Thanks in advance for your help,
> John F
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar