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

[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.

 

[Sylvain Lafontaine]

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.

 

[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

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

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.

 

[Sylvain Lafontaine]

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

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

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

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.

 

[John Vinson]

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]

 

[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

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]

 

[John Vinson]

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]