Create Unique ID from Name field

[rob4465]

Good morning,

I'm in the process of creating some primary Keys in a Access Database before I move them into another system. I'm doing this in Access as it's easier to manipulate the data.

What I have is about 5 thousand company records.

I need to create a Primary Key for each which is no longer than 10 characters.

Test Company ABC
Test Company DEF
Test Company GHI

With each record I can run query to remove the spaces, so we have:

TestCompanyABC
TestCompanyDEF
TestCompanyGHI

What I could do with is then a function to go through each record and create a unique Primary Key. If the same value exists in the table it would need to append a number that increments.

I've made the Primary Key 8 characters, leaving characters 9 and 10 for incrementing numbers.

So, we would have...

TestComp1
TestComp2
TestComp3

Obviously if the Primary Key is unique the function can move onto the next record, otherwise it would need to append a number to make it unique.

Does anyone have any similar function that would help here? Thanks very much in advance.

 

[Ranman256]

Why can't you just use AUTONUM?
It's easy and no work to be done.

 

[plog]

...otherwise it would need to append a number to make it unique

That's the answer for the whole key, not just "duplicates". Use an autonumber and be done with this whole thing.

 

[Insane_ai]

You could add another column to your table for sequencing. Use a Dmax + 1 type statement to increment on new rows. Your decision to use only two characters at the end will limit your scope; are you certain this is all you will need?