Link tables and ID no longer unique

[mickysm]

Hi! Can anyone help?
From an Access I’ve linked to a table in a dBase 5 database. It seems to work fine except that the unique student identifier is no longer unique – there are 360 duplicates out of 2000. Here’s an example of an identifier !)ñ. When I checked my available fonts through Character Map I found that 8 characters in every font were identical and equal to , is this the cause of the problem?

Thanks in anticipation,
mickysm

 

[KenReay]

Hi

Are you saying the Id's are ACTUALLY duplicated or that they LOOK as if they are duplicated? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mickysm]

Hello Ken,
I've been away - hence the late reply.
Thanks for your help; I missed the obvious. Your question ‘do they look the same?’ prompted a solution.

I had related two tables using the ADNO_CODE fields (format of type!#m), but instead of 2000 records I created 600000. Guessing that this was due to lack of uniqueness I ran a ‘Find duplicates’ query and found that Access recognized 360 duplicates out of 2000. I had met this problem before where Access recognized duplicates and the duplicates did look the same: I checked no further and cried for help.

Your response prompted me to investigate further and I discovered the Access did not distinguish between upper case, lower case and superscript. Using queries I created new IDs using
ASC(MID([ADNO_CODE],1,1))&ASC(MID([ADNO_CODE],2,1))&ASC(MID([ADNO_CODE],3,1)).
Nearly; but I still had 6 duplicates - no discrimination between normal script and superscript.

On searching the infuriating Help Files I came across the function ASCB() which is something to do with the Japanese language. As only the third character employed superscript I tried
ASC(MID([ADNO_CODE],1,1))&ASC(MID([ADNO_CODE],2,1))&ASCB(MID([ADNO_CODE],3,1))
Success!!

I appreciate that this is a somewhat inelegant solution: I would be grateful if you could suggest a neater one.
Also, is there any solution to the situation where Access recognizes duplicates and they look the same?
Thanks again,
mickysm