Custom primary key

[a24t42]

I need to come up with a primary key for a table that will contain
letters and numbers. It will be 7 character long. The 1st 3 will be 3PH
and the rest are numbers. Fir example, 3PH0001, 3PH0002, etc. I need
this number to increment by 1 each time a new record is added. Any
suggestions as to the best way to accomplish this?

Thanks in advance.

 

[Matt]

I need to come up with a primary key for a table that will contain
letters and numbers. It will be 7 character long. The 1st 3 will be 3PH
and the rest are numbers. Fir example, 3PH0001, 3PH0002, etc. I need
this number to increment by 1 each time a new record is added. Any
suggestions as to the best way to accomplish this?

Thanks in advance.

How will data be entered into the database ... Forms, ODBC feed, text
file import, etc?

 

[a24t42]

The information will be entered via a form and could have multi users.

24t42

 

[Joseph Meehan]

I need to come up with a primary key for a table that will contain
letters and numbers. It will be 7 character long. The 1st 3 will be
3PH and the rest are numbers. Fir example, 3PH0001, 3PH0002, etc. I
need this number to increment by 1 each time a new record is added.
Any suggestions as to the best way to accomplish this?

Thanks in advance.

Please don't take insult, but I think we should be sure of one thing.
Do you really need that all in a single field as a primary key? It appears
that every record will share the same "3PH" if that is so you don't even
want to have that in a field for each record. You would just display it as
needed, but never store it.

Even if the "3PH" may vary, you may want to keep it in a second field
maybe in a different table, depending on the situation.

 

[Guest]

I'd endorse what Joseph has said insofar as not storing the 3PH prefix in the
table. As its value is constant its unnecessary, but more importantly it
leaves the table open to the risk of update anomalies as the prefix value
could be changed in one or more rows.

Where I'd differ however is that I'd be firmly of the opinion that the
prefix value should be stored in another single row table. It is data and in
a relational database data should be stored only as values at column
positions in rows in tables. This is Date's 'Information Principle' which he
states as:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.

For the table's primary key column you can then have a simple incrementing
number column. When you want to see the full value including the prefix you
can do so in a query such as:

SELECT
Prefix & FORMAT(YourID,"0000") AS FullID,
Field1, Field2 …….
FROM YourTable, Prefixes
ORDER BY YourID;

Where Prefixes is the single row table with a column Prefix, and YourID is
the primary key column of YourTable. The query produces the 'Cartesian
product' of the two tables, i.e. each row in one is joined to each row in the
other.

The above query is not updateable so would not be suitable for the
RecordSource of a data input form. That could be based simply on YourTable
however and include a computed control with a ControlSource of:

=DLookup("Prefix","Prefixes") & Format(YourID,"0000")

Ken Sheridan
Stafford, England