....primary key....w/letters & numbers?

[H]

I'm a newbie w/Access.....learning to use it on my own. I'm curious as to
whether or not the primary (auto numbering) key can also contain letters?
And, if so how do you impliment it into the table? Thanks so much....

H

 

[Kris L.]

You can make any field you like the primary key, and thus, the primary key
can contain letters is the simple answer.
Autonumber is a field type which is almost always used as a primary key
because it is guaranteed to be unique.

The reason this is important is because your primary key must be unique.
Now, if you have a field with letters in it that will be unique, you can
just hit the little "Key" in access to make it primary.

Hope ths helps some.
Kris

 

[H]

Kris...
Okay...but, when using the auto numbering system....can you make it still
give automatic numbers along w/letters. For example: AB-R-01; AB-R-02; etc?
Does that make sense? BTW...thanks for responding.

H

 

[Kris L.]

H -

I doubt this is possible through the actual auto number field.
This may be possible through coding in the form.
This is a little beyond my level of expertise, but I believe you would want
to query the last known record from your table, extract the last ID entered
(your primary key), and then code the field so that the next ID will be Last
ID + 1.

If someone else knows of an easier way to do this, be my guest

Kris

 

[James Hahn]

1. Keep a separate table that contains one item - the last (or next) key in
the sequence. Use the table item to calculate the current key, and update
the item ready for the next use.

2. Keep a separate table of all possible keys (or at least a good subset)
and a flag to indicate which are already used. Then search from the
beginning for the first unused one. This is handy when the keys are complex
(eg, they have a check digit) and you can use some external procedure to
generate the list of valid keys. It also allows keys to be reused
(depending on how you update for cancelled keys) and is simpler in a
multi-user environment (a reserved key that is not used is simply returned
to the pool by insetting the flag).

 

[Van T. Dinh]

1. If the "AB-R-" is always the same, then you don't need to store it in your
database and it possible to use AutoNumber (See point 3).

2. If you have other prefixes like "AB-S-" then it is not possible as you
are likely to require repeated numeric values (See point 3 also).

3. The *only* purpose of AutoNumber Field is to provide uniqueness to each
Record in the Table and the actual value allocated to each Record should not
matter. AutoNumber Field *will* develop gaps (missing numberes in a
sequence), become negative and random. In fact, users of my databases don't
see the values of AutoNumber Field. If you have some (human) meaning
attached to the actual values, then don't use AutoNumber Field as this can
create problems for you later.

HTH
Van T. Dinh
MVP (Access)

 

[Lisa Sokoloski]

Van,
I have a question regarding a similar issue. I am setting up a database at
work. Similar to sales order database. I created a Header table and a
detailed table. My primary key for the detailed table is an autonumber.

I do not understand why the autonumber jumped from 00127 to something like
374,68740! I can believe the numbers will be unique, however, I wanted a
sequencial with gaps only caused by deletion of a record. What went wrong
for me?

Also, in my entry form I have the header table with a subform of detail
table for entry. If for some reason I exit the form and go back to it, the
last entry stays at the end meaning, it appears last no matter what is
entered afterward.

How can these to issues be corrected?

Thanks in advance ,
Lisa Sokoloski

 

[John Vinson]

On Sat, 6 Nov 2004 19:06:01 -0800, "Lisa Sokoloski" <Lisa

Van,
I have a question regarding a similar issue. I am setting up a database at
work. Similar to sales order database. I created a Header table and a
detailed table. My primary key for the detailed table is an autonumber.

I do not understand why the autonumber jumped from 00127 to something like
374,68740! I can believe the numbers will be unique, however, I wanted a
sequencial with gaps only caused by deletion of a record. What went wrong
for me?

Did you perhaps run an Append query? Or Replicate the database? An
Append query may introduce a gap, sometimes a huge one. Replicating
will make all autonumberfields random.

Short take: If you care anything about the value, or plan to display
it to people who care about the value, NEVER USE AUTONUMBERS. They're
going to be unique - but that's *all* that you can count on for their
value. If you want sequential numbers, use a Long Integer field and
program it yourself; see the innumerable threads in this newsgroup
about "Autonumber" or "Custom Counter" using http://groups.google.com
advanced search.

Also, in my entry form I have the header table with a subform of detail
table for entry. If for some reason I exit the form and go back to it, the
last entry stays at the end meaning, it appears last no matter what is
entered afterward.

Table HAVE NO ORDER. They'll usually be presented in Primary Key
order, as a convenience, but on some subforms you can't count on this
either. If you care about the order, base the Form/Subform on a Query
sorted by one or more fields in the table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps