Solved

# Purchase Order Sequencing

Posted on 2012-08-16
298 Views
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
Question by:correlate

LVL 13

Expert Comment

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

Author Comment

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

LVL 74

Accepted Solution

Systems like this can be very tricky to control.

This works in a basic sense:
Access-EEQ27831397AutoNumberbase.mdb
0

Author Comment

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

LVL 74

Expert Comment

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

LVL 13

Expert Comment

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

Author Comment

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

LVL 74

Expert Comment

no problem, the code is fairly basic
0

Author Closing Comment

Apologies for the delay - only had bits & pieces of time to implement this & it works a treat - thank you very much
0

## Featured Post

### Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…