Rounding Issue with dynamic data 1

[kiwieur]

Hi,

I wonder if anyone can help please I am pulling data from a dynamic source and need to find a way to round my figure to a value divisble by a value based on pallet qty.

let me explain, I have created a make table query which is pulling data from several tables in a legacy system such as shown below

TotQty NoOrd AvOrdQty TopUpQty PalletQty
3000 2 1500 =AvOrderQty*3 700

As you can see what I am doing is dividing TotQty by NoOrd to get the AvOrdQty then multiplying it by 3, my problem is that after this is done I need to make sure that the TopupQty is a exact multiple of the palletQty.

Therefore in this instance the topupQty would be 4500 therfore in this case I would need the topUpQty to be either rounded down 4200 or rounded up to 4900.

I have approx 500 rows of data all with different values based on what is extracted from the legacy tables.

Can anyone suggest a way in which I might accomplish this please

Regards

Paul

 

[PHV]

Something like this ?

TopUpQty: TotQty*3/NoOrd+PalletQty-((TotQty*3/NoOrd) MOD PalletQty)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kiwieur]

Hi PHV,
Thank You for the prompt response, I have been in a position to test the code this morning and overall it seems to work OK however I do have a problem that I was hoping you may be able to enlighten me on.

On topup quantities that are not exactly divible by the pallet qty the code seems to work spot on however, where the topup quantity is divisble by the pallet qty it seems to add another pallet qty to it.

I have added some data below to try and show you the problem

TotalCo TotalNoOrd DefPallQty TopUpQty TotalCo/NoOrd*3 PrevCol/DefPallQty TopUpQty/DefPallQty
12100 2 9900 19800 18150 1.83 2
16800 3 4200 21000 16800 4.00 5
14400 4 1200 12000 10800 9.00 10
800 2 400 1600 1200 3.00 4
133159 119 400 3600 3357 8.39 9
6800 6 400 3600 3400 8.50 9
5000 17 200 1000 882 4.41 5

I'm really sorry about the formatting but do not seem to be able to show the tabular data very easy

Regards

Paul

 

[PHV]

Typed, untested;
TopUpQty: TotQty*3/NoOrd+IIf(((TotQty*3/NoOrd) MOD PalletQty)=0,0,1)*PalletQty-((TotQty*3/NoOrd) MOD PalletQty)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kiwieur]

Hi PHV,

Thanks again for the speedy response, the modified code you created for me has done the trick.

I can't thank you enough for your help



Regards

Paul

 

[kiwieur]

Hi PHV,

I am really sorry to trouble you again but I need to tweak the code you so kindly sent me a little if you would not mind helping me.

Basically you helped me to adjust the pallet qty so if it was a whole number then that was used however if it was a fraction it was rounded up to the next whole number.

we have been analysing the data we get from this for the past couple of months and what i need to do now is if the data in my example is not a whole number i would like to round down if it is between .1 and .4 and round up if it is between .5 and .9 so for example

1       =  1
1.49    =  1
1.5     =  2
1.83    =  2
2       =  2
2.1     =  2
2.51    =  3
2.99    =  3
3       =  3

etc.

could you possibly help me with this please

Regards

Paul