I am building a simple bookeeping database on Access 2010 & I need to be able to create an automatic purchase order number which is sequential, but the next number in the sequence is dependant up on the SupplierID rather than the next item input.
Also I need to play around a bit with the formatting of the number, so rather than it running 1,2,3 it runs 0001,0002,0003 ...0010,0011 etc
So by means of an example ....
I buy something today from supplier ABC Supplies ltd, its the first thing I have brought from them so the purchase order number would be ABC-0001. The ABC comes from the SupplierID & the 0001 from the number sequencing. I then buy something from XZY Supplies Ltd, so their PO number is XYZ-0001. 2 days later I buy something else from ABC Supplies, so this PO number would be ABC-0002 etc.
So in order of date input the POs would run ABC-001 >> XYZ-001 >> ABC-002
Hopefully this makes sense - Can anyone help