auto generate second primary key

[Sebastien]

Hi,

I am building a products database, linking sales and production. Each
part has a unique sales Stock Code and Production Number.

The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B). The production
number is a 4 or 5 digits number with first figure(s) indicating
category (BrandA 300 4 Bladed: BrandA is category 1000. This Product
would be 1003 if it was the third created in this category).

I want to use the Sales Stock Code as primary key (OK so far). Then I
want Access to auto generate the production number following these
steps:
1- input new Sales stock Code
2- select category
3- autogenerate production number (if first product from category
3000, give No 3001, second product 3002...).

Thank for your help. Please, contact me if you need more info

Sebastien

[Darryl Kerkeslager]

You talk about wanting to create these fields, but not really what you want
to do with the fields, which I think is a very important question. So
without more information, it's hard to fully answer, but ..

I want to use the Sales Stock Code as primary key The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B)

While this may sound like a good idea, I would caution against this. I know
others have said it, and I can only echo it: every time I've using anything
other than some form of auto-generated numbers for a primary key, I've
regretted it later. You may end up doing contortions with naming to make
your PK unique. Someone may decide that "Bladed" is now referred to as
Knife-edged", so do you alter your primary key? What if you want to add a
product, but don't know for sure that its Bladed - now you can't add it and
edit the details later, because you need *complete* infor to get the PK
right - and you should *never* have to change the PK.

You can have more than one autonumber field, so if you want one for your PK
(a wise choice, my friend ...), you could use another to generate the
production number.

Since you're in the design stage, I'll also say:

For every table I use, the PK is the name of the table + "_id", as in
"offender_id" and "supervision_id". They are always Long Integers,
autonumbered.
I never put spaces in field names, so I never have to use brackets [feeled
name].
I prefix all field names (except the ever-present 'updated' and
'updated_by') with a short version of the table name + "_", so for the
offender table:

offender_id
off_last_name
off_first_name
off_suffix

This way I never have to be concerned with ambiguous references - every
field name is unique in the database (two exceptions above).

Foreign keys are named by the table prefix and the foreign table prefix; in
table address:

add_off_id

This way, I always know exactly what it is - the PK of the offender table as
a FK in the address table. This makes creating joins so much easier, and
keeps names unique.
My 2 shillings.
Darryl Kerkeslager
"Sebastien" <Se*******@jo-at.co.uk> wrote in message
news:cc**************************@posting.google.c om... Hi,

I am building a products database, linking sales and production. Each
part has a unique sales Stock Code and Production Number.

The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B). The production
number is a 4 or 5 digits number with first figure(s) indicating
category (BrandA 300 4 Bladed: BrandA is category 1000. This Product
would be 1003 if it was the third created in this category).

I want to use the Sales Stock Code as primary key (OK so far). Then I
want Access to auto generate the production number following these
steps:
1- input new Sales stock Code
2- select category
3- autogenerate production number (if first product from category
3000, give No 3001, second product 3002...).

Thank for your help. Please, contact me if you need more info

Sebastien

[Pieter Linden]

use DMAX(SomeField)+1 to get the next value and then build the string yourself.

[Sebastien Lagadec]

Thank you Darryl,

I will probably choose an autonumber for my primary key and select my
sales code as a text field with no duplicate (got to redesign my
database :( ).
To give you more info about this prod. number: I need this production
number as it is the code we use to schedule parts in production.
Therefore, it is also unique for each part.
Further, I want to be able able to do some searches to have a full
product description using category, sales code or production number.
Regards,
Sebastien

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Darryl Kerkeslager]

One of the principles of database design is to keep your data in the
smallest meaningful pieces. Doing so speeds up searches and other queries,
usually decreases the size of your database, makes finding meaningful
information easier, allows for better indexing, and other benefits I can't
think of off-hand.
The most basic example, of course, is a name, that you store separately as
first name, last name, middle name, and suffix. Another is splitting
addresses into house number, street, apartment, and state, and zip code,
which also yields obvious benefits.

If you just store all that information in one field, you lose that ability
to make fast and easy queries.

However, I also have found that more often than not, I want to look for the
whole name, or the whole address - so even though it uses space, I also
store the full name and the current address as single fields, so that I
don't have to rebuild the information so frequently. Since speed is more
important to me than space, this makes sense.

My recommendation would be to do the same with your product number: store
each element separately in fields, but also, at the time you enter those
fields, create the full product number and store that in its own field.

Darryl Kerkeslager

"Sebastien Lagadec" <se*******@jo-at.co.uk> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thank you Darryl,

I will probably choose an autonumber for my primary key and select my
sales code as a text field with no duplicate (got to redesign my
database :( ).
To give you more info about this prod. number: I need this production
number as it is the code we use to schedule parts in production.
Therefore, it is also unique for each part.
Further, I want to be able able to do some searches to have a full
product description using category, sales code or production number.
Regards,
Sebastien

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[DFS]

Darryl Kerkeslager wrote:

One of the principles of database design is to keep your data in the
smallest meaningful pieces. Doing so speeds up searches and other
queries, usually decreases the size of your database, makes finding
meaningful information easier, allows for better indexing, and other
benefits I can't think of off-hand.
The most basic example, of course, is a name, that you store
separately as first name, last name, middle name, and suffix.
Another is splitting addresses into house number, street, apartment,
and state, and zip code, which also yields obvious benefits.

If you just store all that information in one field, you lose that
ability to make fast and easy queries.

However, I also have found that more often than not, I want to look
for the whole name, or the whole address - so even though it uses
space, I also store the full name and the current address as single
fields, so that I don't have to rebuild the information so
frequently. Since speed is more important to me than space, this
makes sense.
?? You've just effectively negated all the proper reasoning you displayed
in the first paragraph.

Better that you enter the data once in an atomic form, then "recombine" it
with a query, and do searches against the query.


My recommendation would be to do the same with your product number:
store each element separately in fields, but also, at the time you
enter those fields, create the full product number and store that in
its own field.

Darryl Kerkeslager

"Sebastien Lagadec" <se*******@jo-at.co.uk> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thank you Darryl,

I will probably choose an autonumber for my primary key and select my
sales code as a text field with no duplicate (got to redesign my
database :( ).
To give you more info about this prod. number: I need this production
number as it is the code we use to schedule parts in production.
Therefore, it is also unique for each part.
Further, I want to be able able to do some searches to have a full
product description using category, sales code or production number.
Regards,
Sebastien

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Darryl Kerkeslager]

As I mentioned later, size is less important than speed. While having 2-5
atomic fileds, AND one combined field may take up twice the space, with the
size of hard drives, size on a PC or server is inconsequential. Using both
atomic and combined fields, you get the benefits of fast searches of both
the atomic elements - like a first name or nickname in an alias field AND
you get faster queries on the whole name, since, for instance, you don't
have to re-combine 30,000 names every time you want to print a report with
the complete name.

This is even more important where the combination is not so simple as
first_name + last_name + middle_name + suffix = name. For instance, I have
addresses stored as (possible jail/apartment/other facilty id) +
house_number + street_id + apartement + (city-state-zip id). Each id field
must be queried to obtain the actual text, so building one address is a
complex query; imagine the processing to print out a list of 100 offenders
with addresses - or 1200.

Since I only store the current address in a combined field (not every
address in address history), the actual space used is not so much, and I
still get the speed benefit.

Darryl Kerkeslager

"DFS" <no****@nospam.com> wrote:

?? You've just effectively negated all the proper reasoning you displayed
in the first paragraph. Darryl Kerkeslager wrote:

One of the principles of database design is to keep your data in the
smallest meaningful pieces. Doing so speeds up searches and other
queries, usually decreases the size of your database, makes finding
meaningful information easier, allows for better indexing, and other
benefits I can't think of off-hand. [snip]
If you just store all that information in one field, you lose that
ability to make fast and easy queries.