serial increments miss numbers out

[Hippyer]

Hi, I am trying to set up a database for personal use. I have 4 files Customers, Products, Invoice and Purchases.

In purchases there is no primary key. Customers may order more than 1 product at a time, meaning that there CAN be multiple instances of the 'Invoice ID' (then displayed in a portal in the Invoice database).

At first I had serial set. Next value being 0001 and increment by 1, so that when I wanted to start a new record a new value would be input. This was fine, at the option I have choice of 2 buttons, 'Add another product' or 'Complete invoice'.

So this was fine and I had 1 problem to overcome, when clicking 'Add another product' I needed the 'Invoice ID' to be set as it's earlier value. Looking through all the options I found 'insert from last record' which is great and works fine.

But doing that creates another problem, it still increments the value by one when 0001 is input twice, so it'll skip 0002 and auto enter 0003. This won't cause any mix up with my database it's just that it'd be more organised and just generally better if I could keep track of 'Invoice ID' by increments of 1.

I've been looking for a way of adding 0001 to the value from last record, but not having any luck. Anyone able to help?

Thanks, Hippyer

P.S I hope I stated my problem clearly, please ask if I was a little vague.

 

[D0N]

Hi Hippyer,
A sort of after-the-fact approach, but once you have your records ordered sequencially, have you looked into using the 'replace' command to resquence them?

 

[marty1452]

I was concerned about your comment regarding no primary key in the purchase database. You always need a primary key, always. This is system assigned and not user editable. You may have a secondary key to connect a purchase item to a certain invoice.

Let me know if you are still experiencing any problems.

Marty