Re: Comparing 2 recordsets to get Missing or Different

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Ok, so here's where I'm getting stuck.

I took your example and tried to make it fit my interface:
My interface looks something like this:


cmbModEnv ModEnvDate
(This lets the user choose an environment) (Identifies the extraction)

cmbBaseEnvi BaseEnvDate
(This lets the user choose an environment) (Identifies the extraction)

cmdRunComparison
(Button to take the parameters provided by the user and apply it in VBA
using SQL)


So it would seem like T1 could be the "Mod Environment"
and T2 could be the "Base Environment"

Before I plug these parameters in, I tried modifying the SQL with HARD
values, just to see if I get something - But for the example below, I put how
I plan to use it in my code: (This structure is what keeps erroring out when
I try plugging in real values)

SELECT *
FROM tbl_AIP_ProfileVariables Where Environment = cmbModEnv AND
ActualDateTime = #ModExtractDate# AS T1,

FROM tbl_AIP_ProfileVariables Where Environment = cmbBaseEnv AND
ActualDateTime = #BaseExtractDate# AS T2,

HAVING (((T1.EnvironmentName)=[T2].[EnvironmentName]) AND
((T1.ActualDateTime)<>[T2].[ActualDateTime]) AND
(([T1].[ProfileVariable]=[T2].[ProfileVariable] And
[T1].[PF_Value]=[T2].[PF_Value])=False));










"Tom Ellison" wrote:

> Dear Jon?:
>
> I'm glad if this has been some help. So, we're agreed to create separate
> solutions for MISSING, IDENTICAL, and DIFFERENT. You will be able to UNION
> these later as needed.
>
> By MISSING I believe you mean there is only one row meeting the criteria.
> That is, there is no comparable row to use with it.
>
> It really sounds like your original communication has me on the right track.
> I don't have any suggestions currently for any change in my original
> response. The work of verifying my understanding is really in your hands.
>
> Tom Ellison
>
>
> "jonefer" <jonefer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A1896934-E3B5-4ECD-917A-AC1CB1A98B83@xxxxxxxxxxxxxxxx
> > Dear Tom:
> >
> > I really like where you're going with this so let me take the time to
> > clarify more of the particulars.
> >
> > What I have is a database that tracks the changes made in multiple
> > environments.
> > We have created an interface that enables us to do comparisons against
> > different environments, or the SAME environments at different moments in
> > time.
> >
> > Environment names are like REGNHIM, PRODHIM, STSTHI1, etc...
> >
> > in my particular example, I am giving the example of comparing profile
> > variables from PRODHIM extracted on 12/15/2005 against PRODHIM extracted
> > on
> > 12/03/2005
> >
> > What I really care about is the "Delta" that has occured between these
> > extractions.
> > so, what I'm looking for are the Different Profile values (most likely,
> > the
> > PF_Value and not necessarilly the ProfileVariable - "a label") and the
> > "Missing" profile variables.
> >
> > I totally get where you're going with the descriptive column and UNION
> > query, so you probably don't even need to cross that bridge...
> >
> > But if I could have the basic missing and different SQL statements in
> > place
> > for the MANY comparisons that I am going to have to do, it will be great,
> > knowing that if there are any errors in the results, --it's probably from
> > an
> > incorrect extract!
> >
> > I will try to implement what you have suggested so far, but just in case
> > my
> > clarification has proved a different solution is in order, please show me
> > those examples... again thank you for taking the time to walk me through
> > it
> > in this manner.
> >
> >
> >
> > "Tom Ellison" wrote:
> >
> >> Dear Jon:
> >>
> >> If you have a large number or rows to look at, it would certainly be
> >> easier
> >> to let some SQL code do the walking.
> >>
> >> You seem to indicate you want to compare all rows from within your table
> >> having the same value in one column, Environment.
> >>
> >> There are, as you show, 3 different possible actions: a value of
> >> Environment is found only once, a value is found in two rows and both are
> >> identical in other particulars, or a value is found in two rows and are
> >> not
> >> identical. Unless the value of Environment is found more than twice, you
> >> could also find multiple matches. There could conceivably be 3 values
> >> with
> >> "A" for Environment in one table and 2 in the other. This would result
> >> in
> >> there being 6 compared combinations between the two tables, with each
> >> being
> >> identical or not identical.
> >>
> >> To keep it simple, consider there to be only one other column to compare
> >> for
> >> identical values. This can be very easily extended to compare additional
> >> columns. I'm assuming the Dt (date?) column is not of interest for
> >> comparison. There's not point in whether the dates are the same between
> >> the
> >> two sets, right?
> >>
> >> SELECT *
> >> FROM tbl_AIPProfileVariables T1,
> >> tblAIPProfileVariables T2
> >>
> >> In this first step, we set up a comparison between every pair of rows in
> >> the
> >> table. If there were 100 rows, this "cross product" would have 10,000
> >> rows,
> >> every rows being compared with every other row.
> >>
> >> We can reduce this set by eliminating those rows where the comparison is
> >> between two different values of Environment, as follows:
> >>
> >> SELECT *
> >> FROM tbl_AIPProfileVariables T1,
> >> tblAIPProfileVariables T2
> >> WHERE T1.Environment = T2.Environment
> >>
> >> Also, assuming Environment/Dt is a unique key taken together, we can
> >> eliminate comparisons of any row with itself:
> >>
> >> SELECT *
> >> FROM tbl_AIPProfileVariables T1,
> >> tblAIPProfileVariables T2
> >> WHERE T1.Environment = T2.Environment
> >> AND T1.Dt <> T2.Dt
> >>
> >> Now, this result set can be reduced to show either those that do match or
> >> those that don't match with respect to the other columns. I'm not sure
> >> what
> >> your other columns are named, or how many you have. The example you gave
> >> wraps and is difficult. I'll just call them C1, C2, and C3. You should
> >> be
> >> able to adapt this to your actual column names:
> >>
> >> SELECT *
> >> FROM tbl_AIPProfileVariables T1,
> >> tblAIPProfileVariables T2
> >> WHERE T1.Environment = T2.Environment
> >> AND T1.Dt <> T2.Dt
> >> AND T1.C1 = T2.C1
> >> AND T1.C2 = T2.C2
> >> AND T1.C3 = T2.C3
> >>
> >> This provides any pairs that do match. To see those that don't, negate
> >> the
> >> last 3 rows:
> >>
> >> SELECT *
> >> FROM tbl_AIPProfileVariables T1,
> >> tblAIPProfileVariables T2
> >> WHERE T1.Environment = T2.Environment
> >> AND T1.Dt <> T2.Dt
> >> AND NOT (T1.C1 = T2.C1
> >> AND T1.C2 = T2.C2
> >> AND T1.C3 = T2.C3)
> >>
> >> To find rows without a match:
> >>
> >> SELECT Environment
> >> FROM tblAIPProfileVariables
> >> GROUP BY Environment
> >> HAVING COUNT(*) = 1
> >>
> >> If you want the format of results I think you show in your example, we'll
> >> need to add a column describing "Missing", "Different", and "Identical"
> >> and
> >> then create a union, filling in the missing columns from this last set.
> >> Let's cross that bridge later, OK?
> >>
> >> Tom Ellison
> >>
> >>
> >> "jonefer" <jonefer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:7F212CFE-8F20-4678-960F-06B44DBAE324@xxxxxxxxxxxxxxxx
> >> >I presently have a DAO routine that creates recordsets so I can scroll
> >> > through and find different or missing values between the recordsets.
> >> > I felt I could have the most control with this setup.
> >> >
> >> > However, after being recently exposed to some powerful SQL (see More
> >> > help
> >> > with A Not so simple - MAX function - answer by Ofer)
> >> >
> >> > I would much rather get rid of the code, and construct an SQL query.
> >> >
> >> > I have a table called tbl_AIPProfileVariables
> >> >
> >> > The fields are:
> >> > ProfileVariable
> >> > PF_Value
> >> > Base Environment
> >> > Base Env Date
> >> > Modified Environment
> >> > Mod Env Date
> >> >
> >> > Though it is just one table, the comparison I will be doing is between
> >> > Environments of Different Env Date
> >> >
> >> > For example: I would like to compare Environment "A" extracted on
> >> > 12/15/2004
> >> > 8:30 AM with Environment "A" extracted on 12/03/2004 3:30 PM
> >> > A comparison could also be one between A and B, but for the purposes of
> >> > this
> >> > example, I want to illustrate that it could be between same name
> >> > environments
> >> > with just different Env Dates
> >> >
> >> > and I'd like the result to look something like this: (date has been
> >> > shortened for this example)
> >> >
> >> > ProfileVariable PF_Value Status ChangedFrom Base Base
> >> > Mod
> >> > Mod
> >> >
> >> > Env Dt Env dt
> >> > ========= ====== ==== ========= === === ==== ===
> >> > Send_PT 3 Different 2
> >> > A
> >> > 12/15 A 12/3
> >> > Appt_Count Count all Missing - A
> >> > 12/15 A 12/3
> >> > TimeOfDay Morning Different Evening A
> >> > 12/15
> >> > A 12/3
> >> >
> >> >
> >> > I'm presently walking through the recordsets using DAO...
> >> > but I feel this could be handled more efficiently with SQL?
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: interesting use of NEXT SENTENCE vs. CONTINUE
    ... I am asking how a machine language BRANCH instruction (which has a SINGLE ... can be SLOWER than a machine language COMPARE instruction that has ... If 150 programmers are each compiling 5 COBOL programs ... possibility that ALTERED code in your environment may not be a good thing, ...
    (comp.lang.cobol)
  • Re: WICKED OT: Comments on Gone Baby Gones ending?
    ... single horror on the planet more-or-less instantly available. ... But compare that to the firebombing of Dresden or Tokyo, ... starting to clean up their act in recognition that durable industry ... While I mostly agree with you, I disagree about the environment part. ...
    (alt.smokers.pipes)
  • Re: Change in ASCII graphics
    ... What I would do in your position is compare the environment of both ... Since your problem is related to Midnight Commander, ... This will vary from distribution ...
    (comp.os.linux.misc)
  • Re: OT: Gas Prices -- Help at last?
    ... >> It doesn't take much science to compare before and after. ... destroying the environment. ... Losing 1000 jobs now? ... long-term costs of environmental pollution. ...
    (rec.aviation.piloting)
  • Re: history+readline+ncurses
    ... interface, even if it may not happen to apply to _your_ particular system. ... control sequences even when they do exist, and no C mechanism to probe ... They do not remove the need for system-specific extensions; ... clear what environment she is talking about. ...
    (comp.lang.c)