Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Why? 'Not equal' brings back whats 'equal'

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

Tags:
  1. Gina

    Gina
    Expand Collapse
    Guest

    I am comparing two fields (the same) in two tables. If the first field
    matches in both tables and the second field does not match I want it to
    append from the first table to the second table.


    In a query ... two tables ... two fields that are identical in both tables.
    'Acct #' and 'Closure Date'

    In the 'Closure Date' field under Criteria I have the following...

    <>([History Table ALL].[Closure Date])

    This is what is in the First Table

    1 06-05-06
    255 06-05-06
    255 06-20-06

    This is what is in the Second Table (that I am comparing the First Table with)

    1 06-05-06
    255 06-05-06
    255 06-20-06
    255 06-20-06
    255 06-20-06
    255 06-20-06

    After running the query I get back the result of 255 06-20-06
    This is obviously in the second table, so why does it keep returning this
    value.



    The following is the complete sql script.

    INSERT INTO [History Table ALL] ( [Acct #], [Closure Date], [Reason Code #],
    Exclude, Notes, [Closure Type] )
    SELECT [ccInput Table].[Acct #], [ccInput Table].[Closure Date], [ccInput
    Table].[Reason Code #], [ccInput Table].Exclude, [ccInput Table].Notes,
    [ccInput Table].[Closure Type]
    FROM [ccInput Table] LEFT JOIN [History Table ALL] ON [ccInput Table].[Acct
    #] = [History Table ALL].[Acct #]
    WHERE ((([ccInput Table].[Closure Date])<>([History Table ALL].[Closure
    Date])))
    ORDER BY [ccInput Table].[Acct #];

    Hope I have given you adequate information. If not, let me know.

    Thank you.
    --
    Gina
     
  2. Loading...


  3. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    If you print the selection for the Accnt# and the ClosureDate for *both*
    tables when they are Left Jointed, you will easily see where your error is
    coming from.

    In your case, I would suggest that you use instead the Not Exists (...)
    clause; as it's far more easier to use (and understand) for writing this
    type of queries.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:09936ED7-5748-424B-9C0E-653590B2F554@microsoft.com...
    >I am comparing two fields (the same) in two tables. If the first field
    > matches in both tables and the second field does not match I want it to
    > append from the first table to the second table.
    >
    >
    > In a query ... two tables ... two fields that are identical in both
    > tables.
    > 'Acct #' and 'Closure Date'
    >
    > In the 'Closure Date' field under Criteria I have the following...
    >
    > <>([History Table ALL].[Closure Date])
    >
    > This is what is in the First Table
    >
    > 1 06-05-06
    > 255 06-05-06
    > 255 06-20-06
    >
    > This is what is in the Second Table (that I am comparing the First Table
    > with)
    >
    > 1 06-05-06
    > 255 06-05-06
    > 255 06-20-06
    > 255 06-20-06
    > 255 06-20-06
    > 255 06-20-06
    >
    > After running the query I get back the result of 255 06-20-06
    > This is obviously in the second table, so why does it keep returning this
    > value.
    >
    >
    >
    > The following is the complete sql script.
    >
    > INSERT INTO [History Table ALL] ( [Acct #], [Closure Date], [Reason Code
    > #],
    > Exclude, Notes, [Closure Type] )
    > SELECT [ccInput Table].[Acct #], [ccInput Table].[Closure Date], [ccInput
    > Table].[Reason Code #], [ccInput Table].Exclude, [ccInput Table].Notes,
    > [ccInput Table].[Closure Type]
    > FROM [ccInput Table] LEFT JOIN [History Table ALL] ON [ccInput
    > Table].[Acct
    > #] = [History Table ALL].[Acct #]
    > WHERE ((([ccInput Table].[Closure Date])<>([History Table ALL].[Closure
    > Date])))
    > ORDER BY [ccInput Table].[Acct #];
    >
    > Hope I have given you adequate information. If not, let me know.
    >
    > Thank you.
    > --
    > Gina
     
  4. Gina

    Gina
    Expand Collapse
    Guest

    I have tried 'Not' 'Not In' and '<>' they all give me the same results.

    I am not sure what you are trying to tell me when you say to print the
    selection for the Acct # and Closure Date.

    However, with more trial and error, I am starting to think that in the
    'DATE/TIME' format of the 'Closure Date' - that the query is recognizing a
    time and therefore sees it as being different and not the same. Is this
    possible? If so, how do I tell Access not to acknowledge the time and only
    the date?
    --
    Gina


    "Sylvain Lafontaine" wrote:

    > If you print the selection for the Accnt# and the ClosureDate for *both*
    > tables when they are Left Jointed, you will easily see where your error is
    > coming from.
    >
    > In your case, I would suggest that you use instead the Not Exists (...)
    > clause; as it's far more easier to use (and understand) for writing this
    > type of queries.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: http://cerbermail.com/?QugbLEWINF
    >
    >
    > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > news:09936ED7-5748-424B-9C0E-653590B2F554@microsoft.com...
    > >I am comparing two fields (the same) in two tables. If the first field
    > > matches in both tables and the second field does not match I want it to
    > > append from the first table to the second table.
    > >
    > >
    > > In a query ... two tables ... two fields that are identical in both
    > > tables.
    > > 'Acct #' and 'Closure Date'
    > >
    > > In the 'Closure Date' field under Criteria I have the following...
    > >
    > > <>([History Table ALL].[Closure Date])
    > >
    > > This is what is in the First Table
    > >
    > > 1 06-05-06
    > > 255 06-05-06
    > > 255 06-20-06
    > >
    > > This is what is in the Second Table (that I am comparing the First Table
    > > with)
    > >
    > > 1 06-05-06
    > > 255 06-05-06
    > > 255 06-20-06
    > > 255 06-20-06
    > > 255 06-20-06
    > > 255 06-20-06
    > >
    > > After running the query I get back the result of 255 06-20-06
    > > This is obviously in the second table, so why does it keep returning this
    > > value.
    > >
    > >
    > >
    > > The following is the complete sql script.
    > >
    > > INSERT INTO [History Table ALL] ( [Acct #], [Closure Date], [Reason Code
    > > #],
    > > Exclude, Notes, [Closure Type] )
    > > SELECT [ccInput Table].[Acct #], [ccInput Table].[Closure Date], [ccInput
    > > Table].[Reason Code #], [ccInput Table].Exclude, [ccInput Table].Notes,
    > > [ccInput Table].[Closure Type]
    > > FROM [ccInput Table] LEFT JOIN [History Table ALL] ON [ccInput
    > > Table].[Acct
    > > #] = [History Table ALL].[Acct #]
    > > WHERE ((([ccInput Table].[Closure Date])<>([History Table ALL].[Closure
    > > Date])))
    > > ORDER BY [ccInput Table].[Acct #];
    > >
    > > Hope I have given you adequate information. If not, let me know.
    > >
    > > Thank you.
    > > --
    > > Gina

    >
    >
    >
     
  5. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    If you make a Select query and display the result from both tables, you will
    see something like:

    #1 Date1 #2 Date2
    .....
    255 06-05-06 255 06-05-06
    255 06-05-06 255 06-20-06
    .....
    255 06-20-06 255 06-05-06
    255 06-20-06 255 06-20-06
    .....

    Notice that on the second and third lines, the date from the first table is
    different from the date of the second date, hence these values will get
    inserted again because of your test « Where date1 <> date2 ».

    Instead of a Left Join, you could use something like:

    Insert ...
    Select ...
    From [ccInput Table] as I
    Where Not Exists (Select * From [History Table ALL] as H where I.[Acct #] =
    H.[Acct #] and I.[Closure Date] = H.[Closure Date])

    It's possible to tweak the Left Join in order to have the result that you
    want but it's a little more complicated then that.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:52579EA1-63FC-43EE-9BFF-308C48F469BE@microsoft.com...
    >I have tried 'Not' 'Not In' and '<>' they all give me the same results.
    >
    > I am not sure what you are trying to tell me when you say to print the
    > selection for the Acct # and Closure Date.
    >
    > However, with more trial and error, I am starting to think that in the
    > 'DATE/TIME' format of the 'Closure Date' - that the query is recognizing a
    > time and therefore sees it as being different and not the same. Is this
    > possible? If so, how do I tell Access not to acknowledge the time and
    > only
    > the date?
    > --
    > Gina
    >
    >
    > "Sylvain Lafontaine" wrote:
    >
    >> If you print the selection for the Accnt# and the ClosureDate for *both*
    >> tables when they are Left Jointed, you will easily see where your error
    >> is
    >> coming from.
    >>
    >> In your case, I would suggest that you use instead the Not Exists (...)
    >> clause; as it's far more easier to use (and understand) for writing this
    >> type of queries.
    >>
    >> --
    >> Sylvain Lafontaine, ing.
    >> MVP - Technologies Virtual-PC
    >> E-mail: http://cerbermail.com/?QugbLEWINF
    >>
    >>
    >> "Gina" <Gina@discussions.microsoft.com> wrote in message
    >> news:09936ED7-5748-424B-9C0E-653590B2F554@microsoft.com...
    >> >I am comparing two fields (the same) in two tables. If the first field
    >> > matches in both tables and the second field does not match I want it to
    >> > append from the first table to the second table.
    >> >
    >> >
    >> > In a query ... two tables ... two fields that are identical in both
    >> > tables.
    >> > 'Acct #' and 'Closure Date'
    >> >
    >> > In the 'Closure Date' field under Criteria I have the following...
    >> >
    >> > <>([History Table ALL].[Closure Date])
    >> >
    >> > This is what is in the First Table
    >> >
    >> > 1 06-05-06
    >> > 255 06-05-06
    >> > 255 06-20-06
    >> >
    >> > This is what is in the Second Table (that I am comparing the First
    >> > Table
    >> > with)
    >> >
    >> > 1 06-05-06
    >> > 255 06-05-06
    >> > 255 06-20-06
    >> > 255 06-20-06
    >> > 255 06-20-06
    >> > 255 06-20-06
    >> >
    >> > After running the query I get back the result of 255
    >> > 06-20-06
    >> > This is obviously in the second table, so why does it keep returning
    >> > this
    >> > value.
    >> >
    >> >
    >> >
    >> > The following is the complete sql script.
    >> >
    >> > INSERT INTO [History Table ALL] ( [Acct #], [Closure Date], [Reason
    >> > Code
    >> > #],
    >> > Exclude, Notes, [Closure Type] )
    >> > SELECT [ccInput Table].[Acct #], [ccInput Table].[Closure Date],
    >> > [ccInput
    >> > Table].[Reason Code #], [ccInput Table].Exclude, [ccInput Table].Notes,
    >> > [ccInput Table].[Closure Type]
    >> > FROM [ccInput Table] LEFT JOIN [History Table ALL] ON [ccInput
    >> > Table].[Acct
    >> > #] = [History Table ALL].[Acct #]
    >> > WHERE ((([ccInput Table].[Closure Date])<>([History Table ALL].[Closure
    >> > Date])))
    >> > ORDER BY [ccInput Table].[Acct #];
    >> >
    >> > Hope I have given you adequate information. If not, let me know.
    >> >
    >> > Thank you.
    >> > --
    >> > Gina

    >>
    >>
    >>
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 19 Jul 2006 11:35:01 -0700, Gina
    <Gina@discussions.microsoft.com> wrote:

    >After running the query I get back the result of 255 06-20-06
    >This is obviously in the second table, so why does it keep returning this
    >value.


    Just to explain the logic... which makes sense when you understand it
    but isn't immediately obvious!

    INSERT INTO [History Table ALL] ( [Acct #], [Closure Date], [Reason
    Code #], Exclude, Notes, [Closure Type] )
    SELECT [ccInput Table].[Acct #], [ccInput Table].[Closure Date],
    [ccInput Table].[Reason Code #], [ccInput Table].Exclude, [ccInput
    Table].Notes, [ccInput Table].[Closure Type]
    FROM [ccInput Table]
    LEFT JOIN [History Table ALL]
    ON [ccInput Table].[Acct #] = [History Table ALL].[Acct #]
    WHERE ((([ccInput Table].[Closure Date])<>([History Table
    ALL].[Closure Date])))
    ORDER BY [ccInput Table].[Acct #];

    What this is doing is joining [ccInput Table] to [History Table ALL].
    Every [Acct #] in [ccInput Table] is linked to the corresponding
    records with the same [Acct #] in [History Table ALL], in *all
    possible combinations*. If there are ten records for a given Acct # in
    [ccInput Table] and twenty for that Acct # in [History Table ALL],
    you'll get all 200 possible combinations.

    If the Closure Date in any one of these is different from the closure
    date in the History table, that record gets retrieved. It doesn't
    matter a bit if there are OTHERE records where the dates are equal -
    if it finds one inequality, that's good enough.

    John W. Vinson[MVP]
     
  7. Gina

    Gina
    Expand Collapse
    Guest

    Thank you both for your assistance. Below is what I found to work. I needed
    to join both the Acct# and the also Join the Closure Date fields and then use
    Is Null for the History Table ALL.Closure Date.

    John, Thank you for the extra explaination, it did help to put it in
    perspective. I was expecting a different result than what I was obviously
    querying.

    Again, thank you both for your time.

    Gina


    INSERT INTO [History Table ALL] ( [Acct #], [Closure Date] )
    SELECT DISTINCTROW [ccInput Table].[Acct #], [ccInput Table].[Closure Date]
    FROM [ccInput Table] LEFT JOIN [History Table ALL] ON ([ccInput Table].[Acct
    #] = [History Table ALL].[Acct #]) AND ([ccInput Table].[Closure Date] =
    [History Table ALL].[Closure Date])
    WHERE ((([History Table ALL].[Closure Date]) Is Null))
    ORDER BY [ccInput Table].[Acct #]
    WITH OWNERACCESS OPTION;

    --
    Gina


    "John Vinson" wrote:

    > On Wed, 19 Jul 2006 11:35:01 -0700, Gina
    > <Gina@discussions.microsoft.com> wrote:
    >
    > >After running the query I get back the result of 255 06-20-06
    > >This is obviously in the second table, so why does it keep returning this
    > >value.

    >
    > Just to explain the logic... which makes sense when you understand it
    > but isn't immediately obvious!
    >
    > INSERT INTO [History Table ALL] ( [Acct #], [Closure Date], [Reason
    > Code #], Exclude, Notes, [Closure Type] )
    > SELECT [ccInput Table].[Acct #], [ccInput Table].[Closure Date],
    > [ccInput Table].[Reason Code #], [ccInput Table].Exclude, [ccInput
    > Table].Notes, [ccInput Table].[Closure Type]
    > FROM [ccInput Table]
    > LEFT JOIN [History Table ALL]
    > ON [ccInput Table].[Acct #] = [History Table ALL].[Acct #]
    > WHERE ((([ccInput Table].[Closure Date])<>([History Table
    > ALL].[Closure Date])))
    > ORDER BY [ccInput Table].[Acct #];
    >
    > What this is doing is joining [ccInput Table] to [History Table ALL].
    > Every [Acct #] in [ccInput Table] is linked to the corresponding
    > records with the same [Acct #] in [History Table ALL], in *all
    > possible combinations*. If there are ten records for a given Acct # in
    > [ccInput Table] and twenty for that Acct # in [History Table ALL],
    > you'll get all 200 possible combinations.
    >
    > If the Closure Date in any one of these is different from the closure
    > date in the History table, that record gets retrieved. It doesn't
    > matter a bit if there are OTHERE records where the dates are equal -
    > if it finds one inequality, that's good enough.
    >
    > John W. Vinson[MVP]
    >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 20 Jul 2006 09:50:02 -0700, Gina
    <Gina@discussions.microsoft.com> wrote:

    >Thank you both for your assistance. Below is what I found to work. I needed
    >to join both the Acct# and the also Join the Closure Date fields and then use
    >Is Null for the History Table ALL.Closure Date.
    >


    ah... a "frustrated outer join" query is what I call that. I was
    guessing that was your desired result but wasn't certain.

    John W. Vinson[MVP]
     

Share This Page