How do I add a counter to an existing field?

[mechwarrior3]

Hello everyone,

Here is my problem: I have a primary key field in an Access table. This field keeps track of lot numbers for my company. However, sometimes, we lose the slips that tell the users the lot number. In this case, we need to put in some kind of default value for the lot number. However, it is the primary key field so there can not be duplicates. How do I create some way to put in some kind of default value (like 000000000) and then have it increment as each new missing lot number is added to the database?

Any suggestions are greatly appreciated. I know this is a bit of an odd question.

 

[mechwarrior3]

Resolved

Nevermind. I'm sorry everyone. I did a little thinking and I figured out a way around it. What I am going to do is create another field in my data table that simply keeps track of the counter. Whenever one of those lost lots comes through, my program then turns that counter into a string and appends it to the lot number and then the counter is incremented. Sorry about posting too soon. I was only thinking of how to accomplish my goal without adding any new fields however, this solution was an easy fix. Sorry again for the premature post.

 

[cjard]

not a problem, and its called cardboard analysis - you go and talk about the problem to someone who will have difficulty understanding it. in explaining it in the necessary depth, you think about it more amd realise the answer


I would have said it seems your problem can be solved by the database query itself, but you need some way that your slip number can be programmatically understood

for example, if your lot number was a number, incrememnting like:

001
002
003


and then suddenly you get a ticket that doesnt have a lot number then you can, for example, find the max lot number, add one to it, and use that instead

your problem will come if the next ticket to come along has DOES have a lot number, has 004 instead of 005


if this is the case then it points more to a database design flaw - you have chosen a bad field to perform the job of primary key because it might sometimes be null and PKs cannot be null. find another primary key or compound primary key on the data you have received, enforce a business rule that says "no lot number = no deal" or do not have a primary key at all

 

[mechwarrior3]

You bring up a good point about the possibility of good data actually screwing with the lost lot data. However, I have hopefully avoided that issue with good data being saved as xxxxxx-xxx-x and the lost lot data being saved as 000000000001 instead. Hopefully anyways. But you are right. If good data is saved to the same format as lost lot data, things could get really ugly really quick. Thanks for the advice, cjard.

 

[cjard]

np.. of course in his case you can use something like a sequence.. if your slip number field is varchar (to contain the - signs) then you can have a sequence running from 0 - 1000000 etc, and a function that returns this formatted as
UNK0000001, UNK0000002 etc.. im choosing UNK for UNKnown, but whatever combination of characters you can come up with to ensure that good data wont collide with made up data, will do