Jan, I have worked with lot control for several years, especially as a auditor for the ISO 9000 series standards. I would suggest that you keep this in mind.
A lot control number (or batch number as some call it) has one purpose... to uniquely identify products so they can be traced throughout the manufacturing or distribution process. When considering this, the lot number is then tied to various documents. Such as PO ABC brings in raw material and that "lot" of material gets a number assigned. When a manufacturing order is released to produce product X and the above lot is used, a record is then kept to record that this product X was made from material brought in from PO ABC. This happens so forth down the line, even in the distribution process. So in the end, a finished product can be traced from raw material through distribution. This is done via the lot number. So to come to my point. A lot number in most systems almost always has a sequence of records attached to it. What PO, what manufacturing orders, what shipments, what testing records, etc. etc. The documents is what is important, more so than the lot number. Therefore, I have found in most cases that the lot number itself is not too critical and that a sequential number works fine, I.E. a simple autonumber value. Again, you track the process and related documents, not the value of the lot number. That said, the only reason that a coded lot number is advantageous, is if there are situations where someone physically handling the product needs to read the lot number and determine some information from it without access to a computer. So for instance, you are thinking of using a date stamp plus an integer. I would ask.. what is the date stamp going to be used for? In your process will it actually be looked at and will the date stamp add value to the process? If not, then do not use it. A simple sequential number is more than sufficient as that lot number will need to be tracked through the computer system anyway to documents such as PO's, manufacturing orders, sales orders, etc. etc. So the first question is do you need the date stamp? Secondly, if so, include an autonumber column in your table. You can then either have a computed column = ((ctxt(.#date)) + (ctxt(autonumber column))) or use an after insert trigger to assign the lot number in the same manner. -Bob -- Thompson Technology Consultants LaPorte, IN 46350 219-363-7441 -------------- Original message -------------- From: "jan johansen" <[EMAIL PROTECTED]> > Good day, > > My client finally determined that he wanted to be able to look at a lot > number > (intended for a bar code) and make sense of it. > We settled on YYYYMMDD#####. > However I would like to autonumber that column but can't figure out if thats > possible. I'm thinking I am going to have to programmatically control the > number > or at least the use of a trigger to find the last number of a date used +1). > This is escaping at this time. > Any thoughts appreciated. > > Jan > p.s. considering proposing YYYYMMDDHHMMSS > >
