INNER JOIN query

[MDW]

I maintain a central database (add/delete/edit) using information from a
number of sources - I have created several queries which compare 2 tables
which output any differences between the two. All of them work apart from the
one below

SELECT Pathologyold.*, Pathologynew.*
FROM Pathologyold INNER JOIN Pathologynew ON Pathologyold.ID =Pathologynew.ID
WHERE (((Pathologyold.[Initial biopsy])<>[Pathologynew].[Initial biopsy]))
OR (((Pathologyold.[Initial histology])<>[Pathologynew].[Initial histology]))
OR (((Pathologyold.[Final histology])<>[Pathologynew].[Final histology])) OR
(((Pathologyold.[Lymph node mets])<>[Pathologynew].[Lymph node mets])) OR
(((Pathologyold.Grade)<>[Pathologynew].[Grade])) OR
(((Pathologyold.[Peritoneal washings])<>[Pathologynew].[Peritoneal
washings])) OR
(((Pathologyold.[Path report date])<>[Pathologynew].[Path report date]));

I have tested the query by creating identical tables - pathologyold and
pathologynew and amending the contents of the fields.
The query works for the fields [Final histology] onwards, but refuses to
pick up any changes in the first two fields [Initial biopsy] or [Initial
histology]

Any suggestions ?

 

[[MVP] S.Clark]

Check that the set of fields have the same datatype. It always seems that
people have problems using Memo fields. If these are memo fields, try
making them text255.

 

[John Spencer (MVP)]

Can't really tell, but

Can you cut the where clause down to just the Initial Biopsy field and see what
that gets you?

If that fails to return any record, then I would change one record in
PathologyOld.[Initial biopsy] and rerun the query.

If that fails to return the record, then I would drop any indexes, compact, and
try again.

If that fails, I would go have a beer, a glass of wine, or a long tall glass of
iced tea. Sit back and ponder,

 

[MDW]

Removed all except Initial Biopsy and rerun query didn't pick up differences.

Tables not indexed, but compacted it and rerun - didn't pick up differences.

So i'm having a drink and a ponder

Can't really tell, but

Can you cut the where clause down to just the Initial Biopsy field and see what
that gets you?

If that fails to return any record, then I would change one record in
PathologyOld.[Initial biopsy] and rerun the query.

If that fails to return the record, then I would drop any indexes, compact, and
try again.

If that fails, I would go have a beer, a glass of wine, or a long tall glass of
iced tea. Sit back and ponder,

I maintain a central database (add/delete/edit) using information from a
number of sources - I have created several queries which compare 2 tables
which output any differences between the two. All of them work apart from the
one below

SELECT Pathologyold.*, Pathologynew.*
FROM Pathologyold INNER JOIN Pathologynew ON Pathologyold.ID =Pathologynew.ID
WHERE (((Pathologyold.[Initial biopsy])<>[Pathologynew].[Initial biopsy]))
OR (((Pathologyold.[Initial histology])<>[Pathologynew].[Initial histology]))
OR (((Pathologyold.[Final histology])<>[Pathologynew].[Final histology])) OR
(((Pathologyold.[Lymph node mets])<>[Pathologynew].[Lymph node mets])) OR
(((Pathologyold.Grade)<>[Pathologynew].[Grade])) OR
(((Pathologyold.[Peritoneal washings])<>[Pathologynew].[Peritoneal
washings])) OR
(((Pathologyold.[Path report date])<>[Pathologynew].[Path report date]));

I have tested the query by creating identical tables - pathologyold and
pathologynew and amending the contents of the fields.
The query works for the fields [Final histology] onwards, but refuses to
pick up any changes in the first two fields [Initial biopsy] or [Initial
histology]

Any suggestions ?

 

[John Spencer (MVP)]

The only thing I can think of is that there are no differences on that field.
Did you try to force a difference on one record and see what happens?

Removed all except Initial Biopsy and rerun query didn't pick up differences.

Tables not indexed, but compacted it and rerun - didn't pick up differences.

So i'm having a drink and a ponder

Can't really tell, but

Can you cut the where clause down to just the Initial Biopsy field and see what
that gets you?

If that fails to return any record, then I would change one record in
PathologyOld.[Initial biopsy] and rerun the query.

If that fails to return the record, then I would drop any indexes, compact, and
try again.

If that fails, I would go have a beer, a glass of wine, or a long tall glass of
iced tea. Sit back and ponder,

I maintain a central database (add/delete/edit) using information from a
number of sources - I have created several queries which compare 2 tables
which output any differences between the two. All of them work apart from the
one below

SELECT Pathologyold.*, Pathologynew.*
FROM Pathologyold INNER JOIN Pathologynew ON Pathologyold.ID =Pathologynew.ID
WHERE (((Pathologyold.[Initial biopsy])<>[Pathologynew].[Initial biopsy]))
OR (((Pathologyold.[Initial histology])<>[Pathologynew].[Initial histology]))
OR (((Pathologyold.[Final histology])<>[Pathologynew].[Final histology])) OR
(((Pathologyold.[Lymph node mets])<>[Pathologynew].[Lymph node mets])) OR
(((Pathologyold.Grade)<>[Pathologynew].[Grade])) OR
(((Pathologyold.[Peritoneal washings])<>[Pathologynew].[Peritoneal
washings])) OR
(((Pathologyold.[Path report date])<>[Pathologynew].[Path report date]));

I have tested the query by creating identical tables - pathologyold and
pathologynew and amending the contents of the fields.
The query works for the fields [Final histology] onwards, but refuses to
pick up any changes in the first two fields [Initial biopsy] or [Initial
histology]

Any suggestions ?

 

[MDW]

I originally had 1400 + records in the tables, so I deleted 1397 records to
leave 3 records in each, I then removed all the fields apart from ID and
Initial biopsy. I confirmed that the 3 records in each table (old & new) had
the same ID (cut & paste from one to the other ) and I amended Initial biopsy
in the new table - it picks up if there is a difference between two entries
where both entries already contain text ie [pathologyold][ID]=1,
[pathologyold][Initial biopsy]='test' compared to
[pathologynew][ID]=1,[pathologynew][Initial biopsy]='tested'. However if one
of the [Initial biopsy] is Null ( either in old or new table), and the other
[Initial biopsy] contains text, it doesn't picked this up.

The only thing I can think of is that there are no differences on that field.
Did you try to force a difference on one record and see what happens?

Removed all except Initial Biopsy and rerun query didn't pick up differences.

Tables not indexed, but compacted it and rerun - didn't pick up differences.

So i'm having a drink and a ponder

Can't really tell, but

Can you cut the where clause down to just the Initial Biopsy field and see what
that gets you?

If that fails to return any record, then I would change one record in
PathologyOld.[Initial biopsy] and rerun the query.

If that fails to return the record, then I would drop any indexes, compact, and
try again.

If that fails, I would go have a beer, a glass of wine, or a long tall glass of
iced tea. Sit back and ponder,



MDW wrote:

I maintain a central database (add/delete/edit) using information from a
number of sources - I have created several queries which compare 2 tables
which output any differences between the two. All of them work apart from the
one below

SELECT Pathologyold.*, Pathologynew.*
FROM Pathologyold INNER JOIN Pathologynew ON Pathologyold.ID =Pathologynew.ID
WHERE (((Pathologyold.[Initial biopsy])<>[Pathologynew].[Initial biopsy]))
OR (((Pathologyold.[Initial histology])<>[Pathologynew].[Initial histology]))
OR (((Pathologyold.[Final histology])<>[Pathologynew].[Final histology])) OR
(((Pathologyold.[Lymph node mets])<>[Pathologynew].[Lymph node mets])) OR
(((Pathologyold.Grade)<>[Pathologynew].[Grade])) OR
(((Pathologyold.[Peritoneal washings])<>[Pathologynew].[Peritoneal
washings])) OR
(((Pathologyold.[Path report date])<>[Pathologynew].[Path report date]));

I have tested the query by creating identical tables - pathologyold and
pathologynew and amending the contents of the fields.
The query works for the fields [Final histology] onwards, but refuses to
pick up any changes in the first two fields [Initial biopsy] or [Initial
histology]

Any suggestions ?

 

[John Spencer (MVP)]

Yep. Since NULL is never equal to anything, it is also never unequal to anything.

So, you'll need to add that to the where criteria or force a value in the NULL
fields. Try

WHERE Pathologynew.[Initial Biopsy] & "" <> PathologyOld.[Intial biopsy] & ""

That should handle the problem. I meant to ask you about null values, but it
completely slipped my mind.

I originally had 1400 + records in the tables, so I deleted 1397 records to
leave 3 records in each, I then removed all the fields apart from ID and
Initial biopsy. I confirmed that the 3 records in each table (old & new) had
the same ID (cut & paste from one to the other ) and I amended Initial biopsy
in the new table - it picks up if there is a difference between two entries
where both entries already contain text ie [pathologyold][ID]=1,
[pathologyold][Initial biopsy]='test' compared to
[pathologynew][ID]=1,[pathologynew][Initial biopsy]='tested'. However if one
of the [Initial biopsy] is Null ( either in old or new table), and the other
[Initial biopsy] contains text, it doesn't picked this up.

The only thing I can think of is that there are no differences on that field.
Did you try to force a difference on one record and see what happens?

Removed all except Initial Biopsy and rerun query didn't pick up differences.

Tables not indexed, but compacted it and rerun - didn't pick up differences.

So i'm having a drink and a ponder

:

Can't really tell, but

Can you cut the where clause down to just the Initial Biopsy field and see what
that gets you?

If that fails to return any record, then I would change one record in
PathologyOld.[Initial biopsy] and rerun the query.

If that fails to return the record, then I would drop any indexes, compact, and
try again.

If that fails, I would go have a beer, a glass of wine, or a long tall glass of
iced tea. Sit back and ponder,



MDW wrote:

I maintain a central database (add/delete/edit) using information from a
number of sources - I have created several queries which compare 2 tables
which output any differences between the two. All of them work apart from the
one below

SELECT Pathologyold.*, Pathologynew.*
FROM Pathologyold INNER JOIN Pathologynew ON Pathologyold.ID =Pathologynew.ID
WHERE (((Pathologyold.[Initial biopsy])<>[Pathologynew].[Initial biopsy]))
OR (((Pathologyold.[Initial histology])<>[Pathologynew].[Initial histology]))
OR (((Pathologyold.[Final histology])<>[Pathologynew].[Final histology])) OR
(((Pathologyold.[Lymph node mets])<>[Pathologynew].[Lymph node mets])) OR
(((Pathologyold.Grade)<>[Pathologynew].[Grade])) OR
(((Pathologyold.[Peritoneal washings])<>[Pathologynew].[Peritoneal
washings])) OR
(((Pathologyold.[Path report date])<>[Pathologynew].[Path report date]));

I have tested the query by creating identical tables - pathologyold and
pathologynew and amending the contents of the fields.
The query works for the fields [Final histology] onwards, but refuses to
pick up any changes in the first two fields [Initial biopsy] or [Initial
histology]

Any suggestions ?

 

[MDW]

Thanks, it must have been lucky that my previous queries had all encountered
fields which didn't contain a NULL value - I'll amend these as well, and
remember that NULL is never equal to anything.

Yep. Since NULL is never equal to anything, it is also never unequal to anything.

So, you'll need to add that to the where criteria or force a value in the NULL
fields. Try

WHERE Pathologynew.[Initial Biopsy] & "" <> PathologyOld.[Intial biopsy] & ""

That should handle the problem. I meant to ask you about null values, but it
completely slipped my mind.

I originally had 1400 + records in the tables, so I deleted 1397 records to
leave 3 records in each, I then removed all the fields apart from ID and
Initial biopsy. I confirmed that the 3 records in each table (old & new) had
the same ID (cut & paste from one to the other ) and I amended Initial biopsy
in the new table - it picks up if there is a difference between two entries
where both entries already contain text ie [pathologyold][ID]=1,
[pathologyold][Initial biopsy]='test' compared to
[pathologynew][ID]=1,[pathologynew][Initial biopsy]='tested'. However if one
of the [Initial biopsy] is Null ( either in old or new table), and the other
[Initial biopsy] contains text, it doesn't picked this up.

The only thing I can think of is that there are no differences on that field.
Did you try to force a difference on one record and see what happens?

MDW wrote:

Removed all except Initial Biopsy and rerun query didn't pick up differences.

Tables not indexed, but compacted it and rerun - didn't pick up differences.

So i'm having a drink and a ponder

:

Can't really tell, but

Can you cut the where clause down to just the Initial Biopsy field and see what
that gets you?

If that fails to return any record, then I would change one record in
PathologyOld.[Initial biopsy] and rerun the query.

If that fails to return the record, then I would drop any indexes, compact, and
try again.

If that fails, I would go have a beer, a glass of wine, or a long tall glass of
iced tea. Sit back and ponder,



MDW wrote:

I maintain a central database (add/delete/edit) using information from a
number of sources - I have created several queries which compare 2 tables
which output any differences between the two. All of them work apart from the
one below

SELECT Pathologyold.*, Pathologynew.*
FROM Pathologyold INNER JOIN Pathologynew ON Pathologyold.ID =Pathologynew.ID
WHERE (((Pathologyold.[Initial biopsy])<>[Pathologynew].[Initial biopsy]))
OR (((Pathologyold.[Initial histology])<>[Pathologynew].[Initial histology]))
OR (((Pathologyold.[Final histology])<>[Pathologynew].[Final histology])) OR
(((Pathologyold.[Lymph node mets])<>[Pathologynew].[Lymph node mets])) OR
(((Pathologyold.Grade)<>[Pathologynew].[Grade])) OR
(((Pathologyold.[Peritoneal washings])<>[Pathologynew].[Peritoneal
washings])) OR
(((Pathologyold.[Path report date])<>[Pathologynew].[Path report date]));

I have tested the query by creating identical tables - pathologyold and
pathologynew and amending the contents of the fields.
The query works for the fields [Final histology] onwards, but refuses to
pick up any changes in the first two fields [Initial biopsy] or [Initial
histology]

Any suggestions ?