ID using surname & first name

[ags91]

hi,

i have a simple database with (among otherthings) 3 fields: MemberID, Surname, FirstName. I have read through several articles here and i know that the following is not reccomended due to not being a unique Key field (however due to our record system this is required).

I would like the MemberID to be made from the Surname and the first initial of the FirstName eg Surname = Smith, FirstName = John therfore MemberID = SmithJ.

Any help would be appreciated.

Andrew

 

[tlbroadbent]

You can update the MemberId in this manner.

Update tbl Set MemberID=Rtrim(Surname)+left(FirstName,1)

Rtrim may not be needed. I added it in case the surname had trailing spaces. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[MichaelRed]

Well, Terery's soloution will create the string which you request (although if we are into trimming, why not do both ends of both components?).

I would recommend that you adopt such an "ID" field only as a 'regular' index (e.g. NOT the primary key) and maintain a primary key which CAN be unique MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over