looping in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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,
 
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
 
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
 
Back
Top