Combining Fields to create a unique ID.

[George Hewitt]

Hello,

So today i'm wondering how i would best combine values in a table to produce a 'primary key id number.'

For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233

I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elabroate somemore. Any help is appreciated.

Thanks George

 

[MarkK]

In my opinion, making such a key adds difficulty, and provides no advantage. Just use an autonumber.

 

[George Hewitt]

Well I can see your point but i am using it in a different context to the one given where hundreds of different types of records will be used by multiple clients and having a unique key will help a lot in data entry and will just be alot easier i believe!

Moreover i have been doing some thinking would it make sense to create VBA code to create the field value ID when all data is entered for a record. I.e. Takes appropriate values and makes ID field when record is created.

I'm fairly new to access in general if anyone has any more information they can add
would be appreciated.

 

[MarkK]

If you can name one advantage of using such a key that wouldn't be easier to implement some other way, I'll help you write the code.

 

[George Hewitt]

That's what i'm trying to ask what is the most viable way in implementing this. I assume your saying that it wouldn't be clever to implement what i have proposed.

If i had to describe what im trying to achieve in another way: I want to generate a unique code (which can be PK) based on data in that field so we can tell by the code key factors such as when it was made, or what department it belongs to or location it is relevant too. If this is not possible or too hard to implement for a novice such as myself then fair enough i will workaround it.



Cheers for your help so far.

 

[MarkK]

What is the advantage of your key? It will take effort to create, right? What is the payoff for expending that effort?

Not to display data. Your database contains rich data that you can display at will. Why construct a cryptically coded incomplete version?

Not as a primary key. An autonumber key is easier to use.

So as a key, and as a data display, your coded alpha-numeric handle is out-performed by other options. So why create it? What is that advantage?

 

[jdraw]

George,

I agree 100% with lagbolt. Too often people (especially those beginning with database) try to "outsmart" the principles of database. They attempt to concoct some meaningful combination of fields (usually pieces of fields concatenated) to represent something.
In reality, it is usually a better option to assign a "meaningless" number (autonumber) as PK to make each record unique. And that's all autonumber is used for -- unique records (Autonumber is not sequential and not necessarily positive).

If another combination of fields must be unique, then you make a unique composite index out of those fields. I would suggest you do NOT concoct codes-- which may be meaningful to you but may not always be understood by others, and can lead to maintenance issues. Use your atomic fields --1 fact 1 field - where possible. Use unique composite indexes as appropriate to prevent duplicates.

Good luck.

 

[George Hewitt]

Yes i totally understand what you guys are saying i'm looking at this and many other concepts with databases with the wrong mindset.

And jdraw composite keys are something i haven't really looked at they look useful.

Thank you for guiding me in the right direction guys cheers.

 

[jdraw]

George and Pat,
I did recommend an autonumber PK. I recommend that -(an autonumber PK)-for all tables. What I said was -

If another combination of fields must be unique, then you make a unique composite index out of those fields.

I do not like nor use composite PK.

Also, I do agree with Pat's comments/advice.

Good luck with your project.

 

[George Hewitt]

Sorry for the late reply jdraw I understand this alot better now.

Infact i have deliberately been using autonumber everywhere in my design and it seems to be working fine So cheers.

 

[George Hewitt]

So i have been looking into this matter the past few week. I certainly understand the principals alot better. I can see that making a unique indexed field with the appropriate data values would make alot of sense. I have been searching and looking for material on how to achieve this in Access. What exactly needs to be done? I mean can i use my primary key and the field that value i want and fomat a new field to call these values. I know this sounds really stupid i just don't know how to approach doing this!

For example: Do i make the new field in the table and have it call data values in the format? Then i would index (with no duplicates) it to make it unique.

 

[George Hewitt]

Hmm im thinkng this may possibly involve VB code ? I am a massive noob! ;D

Edit: I figured out how to do this by using an update query with the two fields i want! yay

 

[jdraw]

George,

Just saw your most recent posts this AM. I don't get emails when there are new posts to a thread that I have participated in. So got directed here when searching for "composite" index.

Glad you got it working.