Unfortunately, no. Table A has fully qualified usernames monkey.richmond.corporate
and Table B has a list of the users but it's just the part before the . (period) - monkey
and there's no particular # of characters in the long names. the only commonality is the periods, but some users in the field have 2, some have 3, etc. I don't think there are ever more than 3 or 4 though.
I would do a string compare while loop through the recordset. using the instr function would be your best bet.
Good lord - did I miss something in the original requirements? If not, surely that is one of the least optimal of all the possible solutions. Set based problems rarely gain from non-set based solutions.
Gotta agree with you on the changes to the table although I can't help but feel that there is a deeper design problem here that surrogates will only hide. Having said that, they would be a step in the right direction.
BTW turnertodd - an aside but - what is the longest text length in your memo field? If there are "never more than 3 or 4" then memo seems a tad excessive...
It's really just a dataset that I need to pull the info from - only have to do it once and then use that as a table (without that awful memo field) in the new database. Thanks so much for your help - I was finally able to run it by using pootle flump's suggestion - although it took an age to run! I'm going to have to run it again to return subsets of the data though - the result of the query was too large to export!