Creating a Unique Identifier 1

[Brogrim]

I want to create a unique identifier from a table. I want to take
the Ist letter from the 1st and 2nd name fields.
the first 3 letters from the county field.

When this is accomplished could I use the field as a Primary Key or would I use 2 primary keys. (AutoNumber)

I am trying to create a pin number for each individual customer

1st Name - Farrell
2nd Name - Tormay
County - Monaghan

Pin No - FTMON

All help greatly recieved.

 

[mpastore]

I would do both. Use the key you create from the last name, first name, country as the natural primary key. Use the autonumber as the foreign key in other tables. This way if you ever have to change the length or data type of the natural key you will not have to do a lot of data structure changes in related tables.

Mike Pastore

Hats off to (Roy) Harper

 

[Brogrim]

Thanks Mike,

I understand now how I would link the tables. Could you give me a little help on creating pin number.

I have an idea how to do it, O would use the left function to extract the letters but I don't know where to put this expression, is it put into a query.

Thanks

Alan

 

[BNPMike]

You can't have two primary keys.

Whilst I see Mike's point, I personally would not create another field for a key. I'd only do that where the logical primary key was several fields and therfore messy to use/slow to join. Your PIN No seems ideal as your primary key.

As Mike implies, think carefully about the viabiltiy of this key eg what happens with Farrell's wife Fiona Tormay from Monaghan?

 

[Brogrim]

I would be hoping to add a number to each Pin as well

ie FTMON001M

Fiona Monaghan would look like this

FTMON002F

The F& M standing for male and female.

Could I take a the first 3 digits of an Autonumber

 

[GoDawgs]

I'm gonna disagree a bit with BNPMike...I'd go with the autonumber. Mpastore made a good point...if you only use Pin No as the PK and in the future it's decided that you want one more letter in your "Pin No" field then you would have to update every foreign key in the existing tables to match. I'd make the autonumber the primary key but would also index and not allow duplicates in the Pin No field.

That being said, I actually wouldn't store the Pin No at all since it's really not any new data and doesn't need to be stored...unless you definitely need it (not sure of your complete situation).

As far as creating the Pin No field...you can create it in a query with something like this:

PinNo: left([1st Name],1) & left([2nd Name],1) & left([county],3)

If you need it stored then you'll need to run an update query and update your PinNo field to this value...

Hope that helps.

Kevin

 

[Brogrim]

I am building an application that will hold about 90 fields of information broken down over 14 tables about a client.
All of these realtionships will be 1 to 1. By creating the Pin Number in the 1st Table I then presume I can use it through any of the other tables.

Also, If I create the Pin Number in the query will I have to run an update query every time I add a new client.

I'm new to this, so thanks for your patience.

Alan

 

[GoDawgs]

Yes, you'd have to run the update every time you added a new client...but there really is no way of automatically creating this field if you're entering data directly into the table. If you do this on a form then you'd have no problems running the update or gathering the data before completing the record.

 

[JeremyNYC]

GoDawgs is spot on about the PK stuff. Use an autonumber. If you want something else to show your users, just build it on the fly--otherwise you're just storing duplicate data.

But there is a serious problem with your data model. Why break these things into multiple tables if they are all related one to one to each other? There's no need to do that at all. Just put them all in one table.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Brogrim]

thanks for all the advice, I now no that if I place a text box on the form I can create the PIN Number which is more to recognise a clients name and area so thanks a lot for that.

Jeremy, on the data model is it practicle to have a bout 90 fields in a table. For instance some data relates to

topic a
topic b
topic c of a client.

If a client has only data regarding topic a am I not wasting space by allocating data to topics b & c.

Thanks for all the help. Jeremy I work for a non profit disability organisation in Europe, do you know of any similiar companies to yourselves over here. Keep up the good work.

 

[JeremyNYC]

Brogrim,

Access only allocates the space it needs for a record. If your table has 255 fields (the maximum allowed), and your record only has data in one field, it will only take up about as much space as a record in a table with only one field. There is no practical difference between the two in Access. Indeed, even if you have large text fields and you store only one character, it will take up only that much storage space, not the full space that would be needed for the largest string possible.

Other databases work differently.

I can check around my contact lists for people in your neck of the woods. If you want, head to my site and drop me an e-mail so we can talk business stuff off line, as it's not really appropriate to do on these forums.

Jeremy



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.