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.

 

[Pat Hartman]

I would stay away from composite keys also. The simplest solution for a primary key is an autonumber, period.

Once we get past that, there are reasons to create user-friendly identifiers and I think that is what you are looking for. They just shouldn't be the PK.

Before you embark on something like this, you really need to understand where you are going because changing it down the road will be a b****. First off, you need to decide if you will go with two columns - one text and one numeric or try to squish the field into a single column. I prefer the two column approach because it is cleaner to program and also more flexible. For example, if you decide on a mushed column that will be xnnnnn and next month, you decide you need to accommodate xxnnnnn, you'll have a lot of code to change whereas if you go with the two column approach, the change requires no code. You'll just need to add a row to the table where you define the valid values for x.

You should create a unique index on the user-key though to eliminate the possibility of duplicates and you should NEVER let the use enter the number part. He can choose from a combo for the text part but, your code will always generate the next available number.

There are lots of examples posted here about generating sequence numbers so I'll leave you to find one. Ignore any advice that mentions the DLast() or DCount() functions and go with DMax() since that is the only reliable method.

 

[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.

 

[Pat Hartman]

George,
The meaningful ID should be assigned in the BeforeInsert event of a form and that is the ONLY place the code needs to be assuming that the only way to insert rows is via this form. If you have existing data, you might need to write a query or some code to assign IDs to existing rows and I'm hoping that's what you are talking about

The meaningful ID should have a unique index but it should NOT be the primary key nor should it be used in joins or as a foreign key. Its ONLY purpose is to give users a more intuitive way of identifying a record than using a straight numeric autonumber over which you have little control.