looping in query

[Guest]

morning all,

let me just go straight to the problem:
I have Table "Product list" as below;
ID Prod No Price
1 ABC-123 6.1
2 ABD-345 6.2
3 AHD-352 6.2
4 AHF-135 6.3
5 AYU-633 6.5
6 AIK-543 6.5
7 ARE-565 6.8
8 AKI-902 7.1
9 AWQ-922 7.1
10 ATR-677 7.3

And there is a form prompting a "Total Production" as well as "Fluctuation",
i.e: total Production:10000 and fluctuation:10%, thus the Production range
should be 9000 to 11000.

Now come this term call "Qty break" which is the quantity for whole range of
product base on above production range.
Rules are:
1.(The lowest price in "Product list"table)*(Qty Break) > (Lower Production
range)
2.(the Highest price in "Product list"table)*(Qty Break) < (Higher
Production range)

But there are also some criteria for Qty Break whereby (if possible) it
should ends with more "0" value

Example as above;
Lower part--> 9000/6.1=1475.4
Higher part -->11000/7.3=1506.8

So possible value for qty Break are: 1480,1490 and 1500 (three values
satisfied rule 1 and 2) so in this case program will choose 1500 since it has
more 0's

Anyone know how to this working?

Regards,
jv

 

[Tom Ellison]

Dear JV:

I suggest you first create a query that produces these 3 values for
each row of results. Then, to implement the rules you want for which
of these to choose, write a function that takes these 3 values and
determines the value to be used.

Implementing the rule on the maximum number of zeros would not be so
bad. Count the number of zeros from right to left I expect.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Hi Tom

Thanks for replying. But the thing is that im really new to access thus i do
not know where/how to start from. Would you please elaborate more about your
technique?

Regards,

 

[Tom Ellison]

Dear JV:

First concentrate on producing the 3 columns. Do you know how you
want to do that part? (I'm not sure I do!) Then lets look at the
next step.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Hi Tom,

Thanks God i borrowed this access book from friend.now i managed to find to
upper and lower limit for quantity. now how can i generate list of value that
ends with 0's and pick the one with most 0's on it?

Regards,
jv