Jumat, 17 Agustus 2012

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

 

John F.-

OK, but it would still help to know the fields in your table.

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 9:02 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Counting number of expressions that are <> 0

Ok, thanks for your help. Unfortunately, I can't change anything in the tables
so I have to work with what I have.

John F

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> John F.-
>
> What are the fields in your table? As Bill suggests, you may have a design
> problem.
>
> 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 Bill Mosca
> Sent: Friday, August 17, 2012 7:15 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Counting number of expressions that are <> 0
>
> John
>
> It sounds like you are stuck with 15 rows. If the designer won't let you do
that
> switch to SQL view to add the ones you need.
>
> Too bad the table is so poorly designed. Any of those pairs could have both
> fields true. You shouuld have one field for each pair to prevent that. And it
> should be a long so you could have 3 states: 1,0,Null(for not entered yet).
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "jfakes.rm" <jfakes@> wrote:
> >
> > 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
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar