Help with my database - Unique Id

[samia]

Hello Everyone,

I've data already entered (access/tables) on two different computers, however for the clientid on one computer has a hypen (-) and on another - because of something they couldn't get the hypen to work - so they used the slash (/) my Q is: 1) can I search and convert these to one, 2) is it wise the have / or - in the id?

 

[The_Doc_Man]

so they used the slash (/) my Q is: 1) can I search and convert these to one

Yes, but it is tricker than it looks. Basically, you would need to write an UPDATE query (but could do this without SQL design view...)

For the field that contains the separator you DIDN'T want, you could do the query where your criteria field contained LIKE "*/*" (assuming you did not want to keep the / character.) Then in the UPDATE TO: line for that field, you would have

Left( field, instr(1,"/",field)-1) & desired-character-in-quotes & Right( etc. etc. )

In your help fields, look up InStr, Left, and Right to get the exact syntax, and remember that Instr gives you the position of what you wanted. If you use it by itself for the length as well, you INCLUDE THE CHARACTER YOU FOUND! I.e. Left( field, instr(1,"/",field)) includes the "/" character.

is it wise the have / or - in the id?

I would say no, but that's one man's opinion.

 

[samia]

Thanks DocMan for your reply,

P'se (DocMan & others) this is a database which was designed by someone else and I've just been called in. What I know is the owners dont know a thing about how their database is, and eventually they will want to blame someone. This is why I'm asking for your opinion: should I redo the IDs - do them the right way?! If so, how will I maintain the relationships?

I really need your advise,

Thanks in advance,

Samia

 

[samia]

Anyone else who would like to give it a short??

 

[ecniv]

The 'Replace' function may help you...

 

[samia]

Should I create a new field?

Thnx Ecniv,

Won't it change the (relationship) links? Should I create a new column field for id? Will I be able to create a link relationship?

 

[grantsd]

You're just editing the data in the tables, not their properties or their names, etc. Correct me if I'm mistaken, gurus, but that shouldn't affect your table relationships in any way.