UPDATE query difficulties

[tranchemontaigne]

I am using an update query to recode values stored within a field through the use of a conversion table. The query mostly works, but I am finding that sometimes values are not being recoded.

The conversion table looks something like this:
================
NewCode OldCode
1 A
2 B
3 C
9 Z
================

When I run the update query against a data table of ~110K records I find that all but a handful of codes have been recoded, For example more than 6,000 "Z" values would be recoded to 9, but 21 "Z" values will not be changed. More than 21,000 "B" values would be changed, but 4 would not be changed.

Taking a look at the data types, I see that both the old code and new code are stored in a text data type field with a width of 1 character. With each problematic record, the case of the stored value is exactly the same as the case within the conversion table.

I'm using MS Access 2000 (9.0.2720) on Windows XP Professional. The database has been compacted and repaired several times so I don't think that the database is corrupted.

Any thoughts would be greatly appreciated.

 

[tranchemontaigne]

I figured out the problem - the record index numbers were not unique in all cases. When an index number occured more than once, only the first instance of that index number was recoded.