Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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
0
correlate
Asked:
correlate
  • 4
  • 3
  • 2
1 Solution
 
AielloJCommented:
correlate,

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
0
 
correlateAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
Systems like this can be very tricky to control.

This works in a basic sense:
Access-EEQ27831397AutoNumberbase.mdb
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
correlateAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
AielloJCommented:
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
0
 
correlateAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
no problem, the code is fairly basic
0
 
correlateAuthor Commented:
Apologies for the delay - only had bits & pieces of time to implement this & it works a treat - thank you very much
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now