Using Autonumber Primary Keys with text prefixes

[Imicola]

Hi,

I want to use primary key IDs in my tables which are autonumbers, with a text prefix. I.e. for table tblVillage, the ID's will by V1, V2, V3... and for table tblHumanCases, the ID's will be H1, H2, H3...

I have a few questions:

1. I have heard its not good to do this. Is this true and if so why?

2. I am also having problems creating the relationships to related tables due to the field types. In the parent table the field type is autonumber, but using number as field type in the child table doesnt work, as the ID has text in it, but I cant create the join if the field type is set to text as it is then a different type to the parent table. Is there an easy way around this, or perhaps this is the reason its not good to use primary keys with text and autonumber?

3. Is it possible to create a primary key ID for a related table which concatenates the ID of the parent table, with another unique ID? For example the parent tables ID might be A1 for animal number 1, and the child tables ID might then be A1S1 for animal 1 sample 1, and be linked to the ID A1. Is this possible, and if so how can I go about doing it?

Thanks,

Nicola

[mycall]

>>>>1. I have heard its not good to do this. Is this true and if so why?

First of all i dont think it is possible in access. You would need to use code in real time to go through and make your own autonumbertext. Its prolly not a good idea cos it makes the task a lot more complicated. If you want a text field as a primary key then you are better off having it as a user defined input otherwise having code for when a record is added to go through using string manipulation and determining what would be the next autotext. Ie, u need to know the basics of VB.

>>>>2. I am also having problems creating the relationships to related tables due to the field types. In the parent table the field type is autonumber, but using number as field type in the child table doesnt work, as the ID has text in it, but I cant create the join if the field type is set to text as it is then a different type to the parent table. Is there an easy way around this, or perhaps this is the reason its not good to use primary keys with text and autonumber?

Yes correct both fields need to be the same Data type. If the parent table is an autonumber then it cannot contain text so how could u possibly link it to a field which did contain text??? You will need to change the child table and delete any text out of the PK and then change its datatype to numeric.

>>>>>3. Is it possible to create a primary key ID for a related table which concatenates the ID of the parent table, with another unique ID? For example the parent tables ID might be A1 for animal number 1, and the child tables ID might then be A1S1 for animal 1 sample 1, and be linked to the ID A1. Is this possible, and if so how can I go about doing it?

No its not possible they need to be the same. Data integrity.

>>>>Thanks,

Cheers

[NeoPa]

Hi,

I want to use primary key IDs in my tables which are autonumbers, with a text prefix. I.e. for table tblVillage, the ID's will by V1, V2, V3... and for table tblHumanCases, the ID's will be H1, H2, H3...

I have a few questions:

1. I have heard its not good to do this. Is this true and if so why?

2. I am also having problems creating the relationships to related tables due to the field types. In the parent table the field type is autonumber, but using number as field type in the child table doesnt work, as the ID has text in it, but I cant create the join if the field type is set to text as it is then a different type to the parent table. Is there an easy way around this, or perhaps this is the reason its not good to use primary keys with text and autonumber?

3. Is it possible to create a primary key ID for a related table which concatenates the ID of the parent table, with another unique ID? For example the parent tables ID might be A1 for animal number 1, and the child tables ID might then be A1S1 for animal 1 sample 1, and be linked to the ID A1. Is this possible, and if so how can I go about doing it?

Thanks,

Nicola

This is a strange question - by that I mean it's hard to understand exactly what you mean. Let's try to explain some basic ideas then you can come back with more questions if that seems appropriate.

• An AutoNumber field, by its very nature, is unique.
• Any unique item can be a PK on its own and doesn't require any qualification.
• Adding a text component to an AutoNumber field to create a Primary Key can ONLY be done by including it as a separate field within a composite PK.

To create a relationship between any such table and another table with FKs pointing to this one, the FK would have to be set up as two fields too. A matching text field and a matching number field.
In answer to your question 3 - Yes it is possible. You would use a similar concept (composite keys) to that described above.
This is often used to link two tables in a Many-to-Many relationship where they don't share the same key fields.

[MMcCarthy]

Nicola

Just to clarify on point 3. Although you can concatenate two unique ID's from Table A and Table B as the ID (PK) for Table C. This cannot be one field if you want to create relationships with the other tables. However, you can do the following:

Table A
A_ID (Primary key)

Table B
B_ID (Primary key)

Table C
C_ID (Primary key made up of concatenating the two foreign keys below)
A_ID (Foreign key referencing the primary key of Table A)
B_ID (Foreign key referencing the primary key of Table B)

Although you don't say why you want the letters on the primary keys of the other tables if it is just for display purposes you can do that using the format while only the number would be stored.

An to reiterate the point that's been made twice already you cannot have a relationship between a text and a number field. They must have the same datatype. The reason that numbers are used is that they are indexed fields and numbers will index and sort faster than text.

Mary

[Imicola]

Ok thanks.

I wasn't going to use text in my IDs to begin with, but then discussed my database with a colleague who suggested it might make it easier for people analysing the data at a later point as they will be able to see easily which table the data is coming from.

I think that I will just stick to a normal autonumber, as by adding text to it seems to just confuse matters!

I have worked with databases in the past which have used primary keys which started with text (ie SE06) and then this was followed by an autonumber (ie SE0601, SE0602, SE0603...). In this case it was used to identify records which were being imported from different databases. However, this isn't going to be the case for my database so I'll just simplify it and stick to the normal autonumber!


Thanks again for your help,

Nicola

[MMcCarthy]

Ok thanks.

I wasn't going to use text in my IDs to begin with, but then discussed my database with a colleague who suggested it might make it easier for people analysing the data at a later point as they will be able to see easily which table the data is coming from.

I think that I will just stick to a normal autonumber, as by adding text to it seems to just confuse matters!

I have worked with databases in the past which have used primary keys which started with text (ie SE06) and then this was followed by an autonumber (ie SE0601, SE0602, SE0603...). In this case it was used to identify records which were being imported from different databases. However, this isn't going to be the case for my database so I'll just simplify it and stick to the normal autonumber!


Thanks again for your help,

Nicola

Nicola

I think that's a good idea but if it becomes more important in the future then the only way to do this is you will have to drop the Access autonumber and create your own number programmatically using VBA.

Mary

[NeoPa]

Ok thanks.

I wasn't going to use text in my IDs to begin with, but then discussed my database with a colleague who suggested it might make it easier for people analysing the data at a later point as they will be able to see easily which table the data is coming from.

I think that I will just stick to a normal autonumber, as by adding text to it seems to just confuse matters!

I have worked with databases in the past which have used primary keys which started with text (ie SE06) and then this was followed by an autonumber (ie SE0601, SE0602, SE0603...). In this case it was used to identify records which were being imported from different databases. However, this isn't going to be the case for my database so I'll just simplify it and stick to the normal autonumber!


Thanks again for your help,

Nicola

I think what you're getting hung up on here is the difference between a number and an AutoNumber field.
Numbers come in all shapes and sizes. An AutoNumber field is a specific and tightly defined entity. "SE0603" is not an example of an AutoNumber but of a simple number converted to a string before it is stored.

[hyperpau]

Ok thanks.

I wasn't going to use text in my IDs to begin with, but then discussed my database with a colleague who suggested it might make it easier for people analysing the data at a later point as they will be able to see easily which table the data is coming from.

I think that I will just stick to a normal autonumber, as by adding text to it seems to just confuse matters!

I have worked with databases in the past which have used primary keys which started with text (ie SE06) and then this was followed by an autonumber (ie SE0601, SE0602, SE0603...). In this case it was used to identify records which were being imported from different databases. However, this isn't going to be the case for my database so I'll just simplify it and stick to the normal autonumber!


Thanks again for your help,

Nicola

There is actually a way to get what you want.

Go to the design view of your table.

On the autonumber field, type this in the Format property (including the quotes)
--- "V"0 ------

As soon as access generates the numbers automatically, it will put the
Letter V in front of the number. It works for me so good.

[NeoPa]

Be warned - this will not change the AutoNumber data, just how it is displayed. This may not be important, but you should understand what it means.