Purchase Order Sequencing
Dear Experts
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
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
ASKER
Hi thanks for that, trying to get my head around this and I'm struggling to get how the number sequence is generated, from what I understand from your post is ... the actual PO number sequence works across all suppliers ie if the 4th PO I raise is for a new supplier (ID = DEF) that PO would be DEF-0004 rather than DEF-0001. (I can't see how the Db would know what number to add in next - or am I missing the point ?)
Many thanks
Many thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jeff
This certainly does the trick - I need to look at it in the morning to get to grips in order to plug it into my Db.
The only thing to add at this stage is regarding "This works in a basic sense:" - Well to me it works in every sense & is brilliant!
Thank you so so much I now know I can get this done
Tom
This certainly does the trick - I need to look at it in the morning to get to grips in order to plug it into my Db.
The only thing to add at this stage is regarding "This works in a basic sense:" - Well to me it works in every sense & is brilliant!
Thank you so so much I now know I can get this done
Tom
OK, great.
I had done this before in a previous question, but I could not find the link.
So I made you a new one quickly.
Parts of the code can perhaps be streamlined.
;-)
Jeff
I had done this before in a previous question, but I could not find the link.
So I made you a new one quickly.
Parts of the code can perhaps be streamlined.
;-)
Jeff
correlate,
My initial example had an error. It needs to select the largest PO number that exists for the supplier as such:
SELECT
0 + MAX(PO_Nbr) + 1
FROM
Purchase_Orders
WHERE
Supplier_Code = 'ABC'
Selecting the 0 (zero) provides a starting number in case a PO number doesn't exist for this supplier.
AielloJ
My initial example had an error. It needs to select the largest PO number that exists for the supplier as such:
SELECT
0 + MAX(PO_Nbr) + 1
FROM
Purchase_Orders
WHERE
Supplier_Code = 'ABC'
Selecting the 0 (zero) provides a starting number in case a PO number doesn't exist for this supplier.
AielloJ
ASKER
Hi Jeff
Thanks for your solution - I've been slowly getting this adapted today, the solution you provided definitely works, I just need to get my head around how it works so I can adapt it.
I haven't accepted this as the solution yet as I'm keen to keep this question open in case I need to ask a couple of brief questions on something I can't figure out within the example.
I hope that's not to cheeky, I'm away for a few days & will get back on the case Wednesday / Thursday if thats ok
Best regards
Tom
Thanks for your solution - I've been slowly getting this adapted today, the solution you provided definitely works, I just need to get my head around how it works so I can adapt it.
I haven't accepted this as the solution yet as I'm keen to keep this question open in case I need to ask a couple of brief questions on something I can't figure out within the example.
I hope that's not to cheeky, I'm away for a few days & will get back on the case Wednesday / Thursday if thats ok
Best regards
Tom
no problem, the code is fairly basic
ASKER
Apologies for the delay - only had bits & pieces of time to implement this & it works a treat - thank you very much
Some general guidelines.
1) The 'numeric' portion of your PO should be a string/text type field. This follows the general practice in database design that state unless you're doing calculations with it, it should be a string. This pays off in not having to do formatting every time you use it.
2) Your data model probably has a table for Purchase Orders. You next PO could be generated by selecting the largest PO number for that supplier plus one. Here we have to treat the PO like a number instaed of a string, but it's probably the only place we do so, thus my point in #1 above is still valid. ie
SELECT
PO_Nbr + 1
FROM
Purchase_Orders
WHERE
Supplier_Code = 'ABC'
You can store just the numeric part of the PO number and use the relationship to the Suppliers table to generate the Alpha prefix, or store the whole PO number in the PO's table. Which is better depends on your applications requirements.
Best regards,
AielloJ