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

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13554-why-not-equal-brings-back-whats.html

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554

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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554


"Gina" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
> 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

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
> > Table].[Reason Code #], [ccInput Table].Exclude, [ccInput Table].Notes,
> > [ccInput Table].[Closure Type]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
> > 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
>
>
>

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" 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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
>> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
>> >
>> > 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
>>
>>
>>

On Wed, 19 Jul 2006 1101 -0700, Gina
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
>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]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
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]

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 #]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
WITH OWNERACCESS OPTION;

--
Gina


"John Vinson" wrote:

> On Wed, 19 Jul 2006 1101 -0700, Gina
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
> 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]
>

On Thu, 20 Jul 2006 09:50:02 -0700, Gina
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554
>

ah... a "frustrated outer join" query is what I call that. I was
guessing that was your desired result but wasn't certain.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13554

John W. Vinson[MVP]