Text to Numeric Conversion

[kurasp]

I'm trying to standardize two databases for a client; in one of them buildings are refered to by their full names, in another buildings are refered to by numeric codes, which were assigned in the order in which they were created. Each building also has a number of different units. So in one database I might have "Shady Grove 1," which would be called 0101. "Shady Grove 2" would be "0102," whereas "Thousand Pines 1" would be 0201, and so forth. I need to have at least one constant point of reference between the two databases, so if I could convert the numeric to text or vice versa, I'd be fine. I've tried going about this a couple different ways but can't figure out a way to conserve the very odd numbering system they have. Any help would be greatly appreciated. Sorry for the long post.

[comment]

Auto-entered serial numbers, with absolutely no meaning attached to them, are the best.

[kurasp]

thanks for your help, just to clarify, it sounds like your suggesting that I solve the problem by working around it and simply applying auto-entered serial numbers to both that I can use to relate the two files. many thanks again.

[comment]

I would have said "solve the problem by eliminating it". There's no workaround here - this is the recommended practice. It allows changing the name of a building in ONE place, while still keeping all the links intact.

[kurasp]

great, thanks again for your help.