advanced rounding of purchase orders within query

[Cowboy_BeBa]

Hi All

It's been quite a while since i last did DB work and having a little trouble adding a new module to my old DB.

Basically my DB generates a "Shopping List" which my guys manually go through, it lists all the ingredients we need and the quantities for the months production. We then manually write out a purchase order for the ingredients and fax it to our suppliers (yes, people still use faxes, the few suppliers who still live in the stone age and dont have faxes get phone calls with the order). Anyways this has lead to a lot of redundant work and im trying to save them time by adding a small purchasing module to our database. Nothing fancy, itll basically turn the shopping list into a table, allow the users to edit the order quantities and click a button to generate purchase orders (rather than handwriting them). Might also add a feature that will email the PO to the relevant supplier, but that features on the backburner as its not as important

Currently my problem lies within rounding up of the required quantity. Essentially every ingredient has a minimum purchase quantity and an increment qty. The min purchase quantity is exactly as it sounds, some ingredients we need to purchase 100kg or so before the supplier will even ship it out to us (hell some have a minimum of 1000kg). So id like the round function to round our stockrequired value (in the db its a field simply called "reqd") up to whatever the min ord quantity is. However, if it is above the min ord quantity i need to round up to the nearest inrement. the increment refers to the size of the bag/box the ingredient comes in. Some may come in a 1kg bag, others may come in 10jg or 15, or even 25).

So for instance, lets suppose we have a min of 100 and an increment of 15 and that reqd is 10, id need the function to round that 10 to 100. However if reqd is 101 (or even 100.0005) id need the function to round up the next increment to 115.

Tried playing around with the round function but i cant seem to make it work like that

any suggestions?

 

[June7]

Have to 'roll your own' - a VBA UDF (user defined function).

 

[CJ_London]

bit concerned regarding your example - so just to be clear regarding the example what if the min is 100, increment 15 and required is 150? round up to 160?

but if bags are 15kg wouldn't your 100 be a factor of that (i.e. 90 or 105?) and wouldn't you be buying in those units so would in this case be 10 bags rather 150?/160? kg?

finally - what about existing stock?

 

[arnelgp]

Public Function fncRoundReqd(ByVal minValue As Double, ByVal incrValue As Double, ByVal reqdValue As Double) As Double
    If reqdValue < minValue Then
        'do nothing
    Else
        Do Until minValue > reqdValue
            minValue = minValue + incrValue
        Loop
    End If
    fncRoundReqd = minValue
End Function

 

[arnelgp]

can be simplified to:

Public Function fncRoundReqd(ByVal minValue As Double, ByVal incValue As Double, ByVal reqdValue As Double) As Double
    Do Until minValue > reqdValue
        minValue = minValue + incValue
    Loop
    fncRoundReqd = minValue
End Function

 

[Cowboy_BeBa]

bit concerned regarding your example - so just to be clear regarding the example what if the min is 100, increment 15 and required is 150? round up to 160?

but if bags are 15kg wouldn't your 100 be a factor of that (i.e. 90 or 105?) and wouldn't you be buying in those units so would in this case be 10 bags rather 150?/160? kg?

finally - what about existing stock?

Hi CJ

Sorry for the late reply, yes you are correct, i used a bad example

regarding existing stock, that is already factored in when the query calculates the required value (its actually the amount of ingredients required for our blending plan minus what we already have on hand plus the minimum stock on hadn levels, so that we ideally end the month with our minimum SOH, though in practice that rarely happens)

 

[Cowboy_BeBa]

can be simplified to:

Public Function fncRoundReqd(ByVal minValue As Double, ByVal incValue As Double, ByVal reqdValue As Double) As Double
    Do Until minValue > reqdValue
        minValue = minValue + incValue
    Loop
    fncRoundReqd = minValue
End Function

thanks so muc, gonna try this out now and will let ya know how i go