Creating a unique code by incrementing substring of a number

[Shelley]

I have a database that stores Stock Details for a Designer
boutique. The problem I am having is as follows:

I have set up a table that contains all stock details such
as Designer, Style, Sizes, Colours, Costs etc. For each
record entered I need to generate a unique code that is
recognisable when broken down. For example, any items from
the Designer Fornarina will begin with 15; if the item is
a skirt, the second 2 digits would be 13; the last 4
digits represent colour and size. My code should look as
follows: 151399990110. My problem is trying to assign a
4 digit number for the '9999' that goes after style. So,
if i have 2 skirts that are both Fornarina, the same size
and colour, the '9999' is incremented by one so that I
have a unique code. I would also like to be able to start
from 0001 for each designer/style. I have been able to
assign the first four and last four digits to the code
using a form and query but I'm not sure how to increment
the item number. Can anyone help? I hope I have
explained things clearly. Thanks.

 

[Dan Alden]

-----Original Message-----
I have a database that stores Stock Details for a Designer
boutique. The problem I am having is as follows:

I have set up a table that contains all stock details such
as Designer, Style, Sizes, Colours, Costs etc. For each
record entered I need to generate a unique code that is
recognisable when broken down. For example, any items from
the Designer Fornarina will begin with 15; if the item is
a skirt, the second 2 digits would be 13; the last 4
digits represent colour and size. My code should look as
follows: 151399990110. My problem is trying to assign a
4 digit number for the '9999' that goes after style. So,
if i have 2 skirts that are both Fornarina, the same size
and colour, the '9999' is incremented by one so that I
have a unique code. I would also like to be able to start
from 0001 for each designer/style. I have been able to
assign the first four and last four digits to the code
using a form and query but I'm not sure how to increment
the item number. Can anyone help? I hope I have
explained things clearly. Thanks.

.
Your explanation was pretty good. I am not clear though

how you set up the fields in the tabel is it one field or
three or more. If it is one field use a macro or module
or possibly a query to look for records containing the
same combination of the first and last four digits. if
records exist then search for the largest value within
the record set. once you extablish the largest value
assign the new record the value of largest + 10,000 this
will leave the last four digits unchanged.

 

[Shelley]

-----Original Message-----

how you set up the fields in the tabel is it one field or
three or more. If it is one field use a macro or module
or possibly a query to look for records containing the
same combination of the first and last four digits. if
records exist then search for the largest value within
the record set. once you extablish the largest value
assign the new record the value of largest + 10,000 this
will leave the last four digits unchanged.
.

Dan,
Thanks for the prompt reply. Logically, I can see that
your explanation makes sense but unfortunately I'm not too
clued up on the technicalities of how to achieve this!
Sorry! I'm relatively new to Access and have only dabbled
in VB. Could you point me in the right direction? Your
help is much appreciated.
Shelley

 

[Tim Ferguson]

For each
record entered I need to generate a unique code that is
recognisable when broken down. For example, any items from
the Designer Fornarina will begin with 15; if the item is
a skirt, the second 2 digits would be 13; the last 4
digits represent colour and size. My code should look as
follows: 151399990110.

This is, I'm afraid a Really Bad Idea -- the good news being that it is
also unneccessary, at least as far as storing stuff in the table is
concerned. This is a thing called an Intelligent Key, but it's not a clever
thing to use.

It is easy and normal (in every sense of the word) to create a Primary Key
out of all the fields you refer to, and to allocate a new serial number for
records that have the same designer, item, colour, and size.

You will (not might!) run into all sorts of problems when you have created
a key for a skirt and then, when there are many other records using the
key, discover it should have been shirt...

To create a compound PK, in table design mode you ctrl-click each of the
fields (Designer, Item, ColourID, SizeCode, SerialNumber) and then click
the PK toolbar button.

There is lots of code available to create the SerialNumber, which basically
use the BeforeUpdate event on the form to call a function like

dwNewNumber = 1 + DMax("SerialNumber", "StockItems", _
"Designer=15 and Item=13 and ColourID=99 and SizeCode=99")

HTH


Tim F

 

[John Vinson]

I'll give it a go. The problem is I need the numbers
generated for another database and they need to be in this
format.

That's not really an issue. You can store them properly - in multiple
fields - and then concatenate them in a Query to link to the other
database.