Jumat, 17 Agustus 2012

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
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar