Incremental Numbering

[Guest]

Good Day,

I currently use three tables for my products, all of which have "custom"
order id's.

tblStock - orderid = "NW-" 0000 -- Autonumber
tblcustom - orderid = "KI-" 0000 -- Autonumber
tblSamples - orderid = "SP-" 0000 -- Autonumber

I have been told that using Autonumber inhibits what I do.. can anyone help
me with setting up an alternate method of an incremental numbering system?

Thanks,

Brook

 

[Jeff Boyce]

Brook

If you are trying to push three facts into one field, yes, you will have
problems getting the Autonumber datatype to fit. But you don't want to do
that anyway -- this is poor data design.

A quick glance at your description makes me wonder why you have three
different tables? If you had a single table with one additional field to
hold Stock/Custom/Sample, you could use that field to hold your "indicators"
(whatever "NW", "KI", "SP" represent).

If you want to concatenate the three "facts" together, store them as
separate fields, and concatenate them in queries.

If you want to use a "custom autonumber" (a "roll your own" numbering
routine), check mvps.org, or search Google.com for this topic ("custom
autonumber").

 

[Guest]

The reason for the three tables is because of the three types of "Orders"
that I have for my company.

I would like to have one table with all my orders, but I am unsure how to
set this up. Because for each of my three types of orders, I want the order
numbers to be sequencial.

Any tips for me?

Brook

 

[Jeff Boyce]

Brook

Please re-read my response. Having three "types" of orders is not a reason
for three tables (unless there are so many different
characteristics/attributes among the three that they aren't all really
"orders") in a well-normalized relational database.

If you wish to use a sequence of order numbers that is independent by each
type, you can do that with a custom-built numbering routine. Examples of
this can be found at mvps.org or by Googling for "custom autonumber".

Good luck!

Jeff Boyce
<Access MVP>