comparing two tables with the same field

  • Thread starter Thread starter toby
  • Start date Start date
T

toby

I am stumped and I have looked at the properties of the two fields that
should match and I cannot see anything wrong(or different) with either. I am
attempting to compare two tables of information. Both contain one similar(s/b
the same) field that I am linking on in my update query. The update query
should place "exists on ABC table" in the field I created for this purpose
when I link on the two same fields from each table yet my update query yields
"updating 0 records". I tried the "unmatched" query wizard and I get the same
result, that none match. and I know that at least 3 match!

I did have to split out one of the fields in one table as it contained a
suffix and that was successful(I created a new field and linked on that). so
in both tables have this field contains exactly 10 characters, it's text in
both and I set up all the "general" items at the bottom of the table for that
field the same for both tables (required=no, allow zero length=yes,
indexed=no, unicode compression=no (one was set to yes so I changed it), IME
mode=no control, IME sentence mode=none), I made the length of the field in
both tables 255.

any ideas anyone? I am stumped

thanks.
 
Check for leading and/or trailing spaces in those fields. I'd especially
check the one where you had to parse out the suffix.
 
how do I correct for that? How will I know that there are any spaces in the
fields? I copied and pasted one of each table into Excel to check for
apostropohes and I am not seeing that or anything odd. but maybe the spaces
don't show up in Excel? I think you might be onto something here, I just am
not sure how to solve it or look for that. Please advise when you can.
thanks much!
 
Try using the Trim and/or Replace functions.

toby said:
how do I correct for that? How will I know that there are any spaces in the
fields? I copied and pasted one of each table into Excel to check for
apostropohes and I am not seeing that or anything odd. but maybe the spaces
don't show up in Excel? I think you might be onto something here, I just am
not sure how to solve it or look for that. Please advise when you can.
thanks much!
Click to expand...
 
I am stumped and I have looked at the properties of the two fields that
should match and I cannot see anything wrong(or different) with either. I am
attempting to compare two tables of information. Both contain one similar(s/b
the same) field that I am linking on in my update query. The update query
should place "exists on ABC table" in the field I created for this purpose
when I link on the two same fields from each table yet my update query yields
"updating 0 records". I tried the "unmatched" query wizard and I get the same
result, that none match. and I know that at least 3 match!

I did have to split out one of the fields in one table as it contained a
suffix and that was successful(I created a new field and linked on that). so
in both tables have this field contains exactly 10 characters, it's text in
both and I set up all the "general" items at the bottom of the table for that
field the same for both tables (required=no, allow zero length=yes,
indexed=no, unicode compression=no (one was set to yes so I changed it), IME
mode=no control, IME sentence mode=none), I made the length of the field in
both tables 255.

any ideas anyone? I am stumped

thanks.
Click to expand...

Please post the SQL view of your update query. Are you certain that there are
no leading or trailing blanks in the field? It might be well to update both
tables to set this field to Trim([fieldname]) to remove them.
 
well...I did the trim on both fields in each table, still not working :( I
took a look at the replace function and I'm not seeing how that one would
work in this case. drat, I was so sure this was the answer!
 
I did do the trim on both fields in both tables. Here is the SQL of the trim
of one of them: UPDATE SarahICN_m041709 SET SarahICN_m041709.[new ICN] =
Trim(SarahICN_m041709![new ICN]);

and here is the SQL of one of my update queries:

UPDATE SarahICN_m041709 INNER JOIN Sspnotcv ON SarahICN_m041709.[new ICN] =
Sspnotcv.ICN SET SarahICN_m041709.[exists on IT file] = "on IT file";

I wouldn't need to recreate this update query after trimming would I? I
might try that next but offhand, don't think that would be necessary.

thanks!


John W. Vinson said:
I am stumped and I have looked at the properties of the two fields that
should match and I cannot see anything wrong(or different) with either. I am
attempting to compare two tables of information. Both contain one similar(s/b
the same) field that I am linking on in my update query. The update query
should place "exists on ABC table" in the field I created for this purpose
when I link on the two same fields from each table yet my update query yields
"updating 0 records". I tried the "unmatched" query wizard and I get the same
result, that none match. and I know that at least 3 match!

I did have to split out one of the fields in one table as it contained a
suffix and that was successful(I created a new field and linked on that). so
in both tables have this field contains exactly 10 characters, it's text in
both and I set up all the "general" items at the bottom of the table for that
field the same for both tables (required=no, allow zero length=yes,
indexed=no, unicode compression=no (one was set to yes so I changed it), IME
mode=no control, IME sentence mode=none), I made the length of the field in
both tables 255.

any ideas anyone? I am stumped

thanks.
Click to expand...

Please post the SQL view of your update query. Are you certain that there are
no leading or trailing blanks in the field? It might be well to update both
tables to set this field to Trim([fieldname]) to remove them.
Click to expand...
 
I assume the two to be compared are SarahICN_m041709.[new ICN] &
Sspnotcv.ICN so create three queries like these --
SarahICN_Scrub --
SELECT SarahICN_m041709.*, Replace(SarahICN_m041709.[new ICN], " ", "") AS
SarahICN_Short
FROM SarahICN_m041709;

Sspnotcv_Scrub --
SELECT Sspnotcv.*, Replace(Sspnotcv.ICN, " ","") AS Sspnotcv_Short
FROM Sspnotcv;

SELECT SarahICN_Scrub.*, Sspnotcv_Scrub.*
FROM SarahICN_Scrub INNER JOIN Sspnotcv_Scrub ON
Sspnotcv_Scrub.Sspnotcv_Short = SarahICN_Scrub.SarahICN_Short;
 
I did do the trim on both fields in both tables. Here is the SQL of the trim
of one of them: UPDATE SarahICN_m041709 SET SarahICN_m041709.[new ICN] =
Trim(SarahICN_m041709![new ICN]);

and here is the SQL of one of my update queries:

UPDATE SarahICN_m041709 INNER JOIN Sspnotcv ON SarahICN_m041709.[new ICN] =
Sspnotcv.ICN SET SarahICN_m041709.[exists on IT file] = "on IT file";

I wouldn't need to recreate this update query after trimming would I? I
might try that next but offhand, don't think that would be necessary.

thanks!
Click to expand...

What do you see if you just use a select query:

SELECT SarahICN_m041709.[new ICN]
FROM SarahICN_m041709 INNER JOIN Sspnotcv ON SarahICN_m041709.[new ICN] =
Sspnotcv.ICN;

And are you just selecting the datasheet view from the update query (which
will show the records before updating) or actually executing it (clicking the
! icon)? (sorry if this is a silly question, it's an easy mistake to make).
 
Actually? I just solved the problem and I'm not sure why I didn't see it
earlier. well, I am a little sure. the suffix has always been TWO characters
but for some odd reason one file that I was working with, the one I was
trying to compare with the other, had THREE characters split off from the
end. good gawd. so....that was it. I was comparing a field of 10 characters
(which the other SHOULD have been, grrrrr...) with a field of 9 characters.
so, I remedied it by redoing my query to split off 9 of the 12 original
characters instead of 10. oy, my head hurts now! but thanks for your help.
I did learn alot today so it wasn't all for naught!

John W. Vinson said:
I did do the trim on both fields in both tables. Here is the SQL of the trim
of one of them: UPDATE SarahICN_m041709 SET SarahICN_m041709.[new ICN] =
Trim(SarahICN_m041709![new ICN]);

and here is the SQL of one of my update queries:

UPDATE SarahICN_m041709 INNER JOIN Sspnotcv ON SarahICN_m041709.[new ICN] =
Sspnotcv.ICN SET SarahICN_m041709.[exists on IT file] = "on IT file";

I wouldn't need to recreate this update query after trimming would I? I
might try that next but offhand, don't think that would be necessary.

thanks!
Click to expand...

What do you see if you just use a select query:

SELECT SarahICN_m041709.[new ICN]
FROM SarahICN_m041709 INNER JOIN Sspnotcv ON SarahICN_m041709.[new ICN] =
Sspnotcv.ICN;

And are you just selecting the datasheet view from the update query (which
will show the records before updating) or actually executing it (clicking the
! icon)? (sorry if this is a silly question, it's an easy mistake to make).
Click to expand...
 
I actually just now discovered the issue: some idiot trimmed that field to
NINE characters instead of TEN! I have never seen this before! and only
just saw it now, so, problem solved!

thanks for your help. I learned alot today!

KARL DEWEY said:
I assume the two to be compared are SarahICN_m041709.[new ICN] &
Sspnotcv.ICN so create three queries like these --
SarahICN_Scrub --
SELECT SarahICN_m041709.*, Replace(SarahICN_m041709.[new ICN], " ", "") AS
SarahICN_Short
FROM SarahICN_m041709;

Sspnotcv_Scrub --
SELECT Sspnotcv.*, Replace(Sspnotcv.ICN, " ","") AS Sspnotcv_Short
FROM Sspnotcv;

SELECT SarahICN_Scrub.*, Sspnotcv_Scrub.*
FROM SarahICN_Scrub INNER JOIN Sspnotcv_Scrub ON
Sspnotcv_Scrub.Sspnotcv_Short = SarahICN_Scrub.SarahICN_Short;


toby said:
well...I did the trim on both fields in each table, still not working :( I
took a look at the replace function and I'm not seeing how that one would
work in this case. drat, I was so sure this was the answer!
Click to expand...
Click to expand...
 
Back
Top