Transferring Data between Fields

[jimart]

Hi All

I Have a table which contains two fields with a person's Surname & initial(s) as someone's Spouse, and another two which contain the same person's Surname & Firstname as a Parent - the first name field has their full first name in this case.

What i am trying to do is compare the surname & the first initial of the spouse with the surname & first letter of the firstname of the parent & if they are the same, replace the first initial with the first name & add on to that any other initials that there may be...

Thanks

 

[Jack Cowley]

Hmmmm. What if you have Smith R. and Smith, Ruth and Smith, Rose and Smith, Ruby, which is the correct first name for that particular Smith R?

You can parse the names using the Left(), Right(), Mid() and InStr() functions in Access and this article should help you get started...

Jack

 

[jimart]

Whoops! Forgot the punchline....

The names would not be in the same record as each other - they could be anywhere in the table so would have to find them first before matching them.

Also to make sure I had the correct match, i would probably have to match the Father/Husband as well - is this possible in code

Thanks - jimart

 

[Jack Cowley]

All I can say is you have your work cut out for you. Trying to find matches is not too hard but finding the right ones (all of them) and putting them all together is going to be some fun, but I am sure it can be done...

Jack

 

[neileg]

Just to add to Jack's pessemism, how will you deal with divorces and remarriages? The parent relationship won't change but the spouse will.