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.
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.