Autonumber with letter prefix? 1

[and]

Hi there,

I have a field called ClientID that is an AutoNumber. Is there a way I can have this number so that it is prefixed with a letter (that will remain constant)?

Is this going to cause problems if this field is my PK?

TIA

Andrew.

 

[jksmi]

Hi, unless you create your own bespoke function to create an auto alphanumeric there isn't a built in function. Why do you want your primary key to be alphanumeric? Perhaps there is a better way of achieving your goal...

HTH, Jamie
FAQ219-2884

 

[mph1]

Jamie is correct! The following is an explanation of the use of an autonumber:

http://www.mvps.org/access/general/gen0025.htm

HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[jksmi]

Hi, that is an excellent explanation!

HTH, Jamie
FAQ219-2884

 

[and]

Yeah, I figured this was iffy. I am new...finding my Access way...

Maybe you guys can shed some light on my path....

I am building a DB that records clients registered into a programme. There will be 4 stand-alone replica DBs not networked recording clients are 4 different locations. 4 different users (advisors) will be inputing data and client they register will be 'theirs'.

I have field ClientID - PK, autonumber.
I wanted to use this as a number that is assigned to each client as their ID for the programme, unique across all four locations, hence the idea of putting an letter prefix (advisor's initial) to identify the client and their advisor with one ID. But reading that helpful link posted I realise the folly in this (meaningful autonumbers for users being bad practice)

I want to have a unique global clientID across all four DBs (so that records might eventually be compiled together without duplication...)....but I want this ID to be autogenerated by the DB, not inputed by the user.

Should I have:
Autonumber, PK for each DB for referential purposes
ClientID: how do I make this auto gen and unique across all four DBs?

Still not quite getting the plot I think.......
?

Andrew.

 

[jksmi]

Hi,

if your using replication (and I may be wrong here but...) I'm pretty sure that the autonumber can change during the sync process - so automatically generating an ID is a bit of a challenge!

I presume that you wont be inputting national insurance number? Will they be required to input an email address? Presumably your advisors will/can be in the field so you wont be able to use a linked table to a shared server?

Since the client belongs to the advisor an incrementing id made up of advisor id & maximum( of advisor's clients id) + 1 should work... you'd have to create a bespoke function but you'll get plenty of help in the forums (and I know what I mean - but you might have other ideas or better suggestions before I go ahead and put something together!)

Sorry if this doesnt make sense - I keep getting interrupted by people that think I should be doing some real work...

HTH, Jamie
FAQ219-2884

 

[mph1]

Hi Andrew,

What you want to do CAN be done. However, if your knowledge of Access is limited (no disrespect intended), I would suggest creating one database, first to make sure it is working as you want. Since you want to use the same ID number, computer generated, how will each of the 4 users know what has been used?? You may want to consider taking the first db and spliting it into a FE (front end) and BE (back end) and have the BE on a server. This BE will contain all of the tables. The others should be networked. This way you will only need ONE database with copies of the FE on the other PC's.
If I understand correctly what you want, ie, 4 seperate db's independent of each other where you then want to combine data for future evaluation, then all I can invision is confusion.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[randy700]

OR...

You can use the autonumber for each client and include another field (advisor, site, whatever). Give it a default value and don't make it available to the users to change. Use the combination of the two fields as your primary key. Then, if/when you merge the four tables, you won't duplicate your primary key.

So, what you have is
Site Client Name etc
A 1 Smith PK = A1
A 2 Jones PK = A2
B 1 Wilson PK = B1


Randy

 

[and]

Thanks Jamie and mph....

I think a bit of a conceptual rethink by me and The Man might be in order. Seems that we could be getting ourselves into a work of chaos.
Big thanks for pointing me in..or away from...the right direction.

Until next time. I'll be back when I know what I'm doing....

Andrew.

 

[and]

Ah Randy,
That sounds useful. I will look into that method.
Many thanks,
Andrew.

 

[jksmi]

Hi Andy,

just checked, autonumbers in replicas dont change (was wrong) but are random rather than incremental and range from –2,147,483,648 to 2,147,483,647 if set to long or –32,768 to 32,767 if set to integer (half right then). Randy & I are talking about pretty much the same thing - I was just avoiding autonumbers because of this - but to do so adds a level of complexity...


HTH, Jamie
FAQ219-2884

 

[spud3712]

I have used autonumber with a letter prefix. I have five different tables set up, each with an autonumber. You have to format your entry in the table. The format should look like this "\A000" for example. The backslash shows the complete entry, the A should be substituted for whichever letter you want and the zeros hold places for the digits you want. I have used this as my primary key with no probles so far.