ACCESS FEED: RE: [MS_AccessPros] Counting number of expressions that are <> 0

Jumat, 17 Agustus 2012

RE: [MS_AccessPros] Counting number of expressions that are <> 0

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