Jumat, 17 Agustus 2012

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

 

John F,

Apparently, you wish to identify matching records from two queries carrying identical data set, where values in some of the fields are not equal. At the same time, records where values in all fields are equal, are to be ignored so as not to clutter up the output.

We could consider an interesting solution, taking advantage of the fact that UNION clause in a query (without using ALL qualifier) automatically ignores exact duplicates. At the same time, source queries can be identified by assigning identical identifiers in different case (upper & lower case) as the comparison is not case sensitive.

For example let there be two queries Q_1 and Q_2 with large number of identical fields. Let StID be the name of field uniquely identifying each student. First stage union query Q_Comp_A, as given below, will provide an output covering:
(a) Single row for each record that has identical values in all fields in the two queries.
(b) Double row (one per query) for each record that has differing values in any field in the two queries.

Q_Comp_A (First Stage Query)
=============================
SELECT "A" AS Source, * FROM Q_1
UNION SELECT "a" AS Source, * FROM Q_2;
=============================

For better legibility, final query Q_Comp_B, as given below, retains only those sets of double rows per StID, that have differing values in any field in the two queries.

Q_Comp_B (Final Query)
=============================
SELECT Q_Comp_A.*
FROM Q_Comp_A
WHERE ((((SELECT Count(*) FROM Q_Comp_A AS Q WHERE Q.StID = Q_Comp_A.StID))>1))
ORDER BY Q_Comp_A.StID, Q_Comp_A.Source;
=============================

SQL given above is universal in nature and takes care of any number (or names) of fields in the two queries. For example if at any stage, the number of fields increases from 15 to 100, you don't have to alter the suggested SQL.

With this arrangement, you would get an output displaying only those records where there is value difference between the two queries in any column. Source "A" signifies query Q_1 while Source "a" stands for query Q_2.

Best wishes,
A.D. Tejpal
------------

----- Original Message -----
From: Jeff Jones
To: MS Access Professionals
Sent: Saturday, August 18, 2012 01:51
Subject: Re: [MS_AccessPros] Counting number of expressions that are <> 0

Hi John,

I don't see anything referencing the way that the database itself is being used. Is the database split into a FE and BE with each user having their own FE? If so, good if not, I'd see if it could be done. The following might work.

Rather than to try to build one big honking query, technically it's possible to pull the data together into a new, temporary table that is designed so that the results can be achieved. I am suggesting that the temporary table or tables be designed to support the reporting needs. I'm not certain where the selected rows that meet the 15 criteria are displayed. It would seem that a button on the form is used to find the unmatched rows so rather than just execute the query, the first thing it could do would be to delete all data from the temporary table(s) and re-populate them from the current tables with the final step being to select and display the unmatched rows.

Assuming that the database is split, I'd place the temporary tables in the FE so that each user can have their own copy.

I may be way off base because of the assumptions I've needed to make but it's fun to suggest an alternative to "fixing" the existing table design.

Jeff

----- Original Message -----

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

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar