INNER JOIN query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ?
 
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.
 
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,
 
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


John Spencer (MVP) said:
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 ?
Click to expand...
Click to expand...
 
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

John Spencer (MVP) said:
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 ?
Click to expand...
Click to expand...
Click to expand...
 
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.

John Spencer (MVP) said:
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

John Spencer (MVP) said:
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 ?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top