Altering a table

[Paulsburbon]

Hello, I use a database at work that I made. Unfortunatly, I did not add a field for numbering the items that a customer orders. When entering an order It is important to my customers to keep it in a certain order. how do I change it so it adds a numbering to all the past orders as well? I've been trying to think of a way for months and cannot do this on my own. Thank you to anyone who takes the time to help me out. If you have any questions I'll try to answer them the best I can.

 

[Robert88]

Hi Paulsburbon,

Firstly I would make a back-up, always best in case things go wrong.

Then I would open the table in design view and insert an additional field with the properties of autonumber.

I am pretty sure that when you then close the table it should autonumber existing records and create new autonumbers for new future records, do not quote me on this but give it a go.

Once you have the numbers in the table I suppose you will also have to make relevant changes to queries and forms that you use in order to display this new field.

Good Luck.

Happy to help further, let me know what happens.

Rob

 

[Paulsburbon]

Hi Robert, Thanks for the reply. I thought about doing this. The only thing is I was hoping for a way to start at 1 for every new order. So If I was to add an autonumber field I to my order detail table it would start at 1 and keeping going. So say my second order I ever did the first item on it would have the number 10 in it instead of starting at 1 again. Am I making myself clear here? I'm not the best when describing what I need. I also need to be able to place a new record inbetween two. I don't think autonumber would let me do that. If I had an order with three records in it and I need to place the newest one bewteen the first and second I thought I could make the new record 1.5 or something like that. This might be alot to ask but I'm out of idea's myself. Thank you again for your time in reading this.

 

[Robert88]

Question, the number you refer to, that you want to add is it currently somewhere in the table you have or is a number you would manually have to add to existing records?

Look forward to your response.

 

[Paulsburbon]

Thanks for the reply. I do not have it in the database anywhere. It is something I want to add. Thanks again.

 

[Pat Hartman]

There is no automatic way to do what you are asking. I solved a similar problem by adding a user sequence field. As records were added to the table, my code incremented the sequence by 10. However, the user could override the number to change record 30 to 15 to place it between 10 and 20. I then gave the user a button to renumber the records so my code looped through the set (backwards - this is important) and renumbered each record. You do the renumbering backwards so you will avoid generating a duplicate number and causing an error. The user number field needs a unique index (in combination with the order) to prevent duplicates so renumbering becomes tricky.
10
15
20

if you renumber from low to high, 15 becomes 20 but 20 already exists and that causes an error. So renumbering from high to low requires that you count the number of rows so you know what the highest number will be.

Before getting involved in this, make absolutely certain that this is necessary and be sure the customer understands the cost. In my case, the list of items were chemicals and the sequence field specified the order in which they were added to the mixing vat so there was no existing data field that could be used for sorting purposes. The customer needed to specify the incorporation order.

 

[Paulsburbon]

Wow, That was an amazing solution. Thank you so much for your help. I'm going to give it a try on a backup of the data base over the weekend. When I get some time. Thank you again. This forum is a life saver for me.