Creating a sustaining counter as in work orders - unique

[ck9663]

You are awesome and I appreciate your help. I am not very versed in SQL lingo and so defining the problem is a bit of a challenge for me.

I am using proprietary software and trying to pull from it into another package without incrementing the values in the proprietary software.

If I create a new table and add a int column, then on day 2, how do I get the counter to NOT reset? My logic is not such that I can remove the previous day's work orders yet so I will be building and building this table adding new work orders every day. Maybe a better question would be if I create a new table on day 1, how do I add to it on day 2, day 3, keeping the counter int going?

Jo

Jo...

In order to keep this value, you have to store it somewhere. What do you with the daily tables? Do you have a one master table that contain it all? if you do, you can take the max(counter)+1 on that master table as the starting counter on your daily table. for this, you might need a trigger to handle the counter on your daily table. make sure that your counter is the PK on both table to ensure uniqueness.

[Purple]

Hi Jo,

I suggest you have a master workorder table which holds all of the work orders created for all of the business areas and this a permanent table not a temp table..

When you create the table workOrder add a field as the primary key and set it as an auto increment field, this will be the work order id.

Now when you insert rows into the table the work order id field is automatically incremented by one for every new row. (dont try to set a value for this field on the insert, if you want the numbers to start from a specific value, ie other than 1 specify a seed value)

Also create a field to represent the business unit as an int and use a join to the business unit table where you may have columns like

buId buName buContact etc...

I would also reiterate my suggestion to take some time out of the coding work to reconsider the database structure - Mary (one of the site administrators) has written this article which you may find helpful..

Regards Purple