Need help with this

[vinochand]

Hi all,
I am working on developing an application for my company and I have
got
struck up with a step where, it looks like, I need to do a loop
through my
records. I have given a sample recordset similar to the one I have
below
where I have 5 fields and the fifth field in that, which is called
"Total" is
going to be the calculated field and that's where I need a looping.
What that
field signifies is Total==(quantity*Percent) if 'Number' field=0;
else for
each Order Type: (quantity-number)*percent)...in this case it would be
25. I
have given the coding that I tried below the sample data. But
apparently it's
not the way to appoach it. Can any of you suggest why it's not working
and
maybe a better way of achieving this? I would really appreciate your
help on
this. Thanks!

Order Quantity Percent Number Total
A 100 50% 25
A 100 50% 25
A 100 50 50
B 200 50% 100
B 200 50% 100

Private Sub cmdCalc_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim Order As Field
Dim quantity As Field
Dim number As Field
Dim total As Field
Dim percent As Field
With rs
..CursorType = adOpenDynamic
..Open "sheet1", CurrentProject.Connection
End With
Do While Not rs.EOF
For Each rs!Order In rs
If rs!number = 0 Then
rs!total = rs!quantity * rs!percent
Else
rs!total = (rs!quantity - rs!number) * rs!percent
End If
Next rs!Order
Loop
End Sub

 

[Alex Dybenko]

Hi,
you can just make a query with following SQL:

Select *, iif(number=0, quantity * percent, (quantity - number) * percent)
as Total
From sheet1

or even:

Select *, (quantity - number) * percent as Total
From sheet1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Hi,

I cannot do that because it's little complicated than that. I will try to
explain the table in another way to make it clear. Let's say I need to get
100
parts of type X and I have decided to get 20% from supplier A; 50% from
supplier B and 30% from supplier C. So the total field, in this case, would
be (100*20%) for Row 1; (100*50%) for Row 2; (100*30%) for Row 3. But in
some case rather than giving the supplier type in percentage, we may give the
data
in Numerical quantity. For example, let's say, I need another 200 parts in
TYPE Y and I am going to get 20% from supplier A; 80% from supplier B and
also I have a special request here which says, out of those 200 parts I need
to get 100 parts from Supplier C(this is the "Number" field...and this is not
the actual name...actual name is SplRequest...I named it this way here for
convenience). That means, I will only get 20% of 100 parts from supplier A
and NOT 20% of 200 parts. So my total field, in this case, would be
((200-100)*20% for Row 1; ((200-100)*80%) for Row 2; and 100 parts for Row 3.
I have reproduced the table here again for convenience:

Type Quantity Supplier Percent Number Total
X 100 A 20% 20
X 100 B 50% 50
X 100 C 30% 30
Y 200 A 20% (200-100)*20%=20
Y 200 B 80% (200-100)*80%=80
Y 200 C 100 100

So my point is the update command would work fine if I don't have the "Type"
field. But, with the Type field, it's like each "Type" acts as a group. In
other words, if I just say Total=PerCent * (Quantity -
IIF([Number]>0,p[Number],0)), it would apply that to all the records which is
not the desired case as you can see in the table(If it had been that way,
field corresponding to Type Y and supplier A would have been 40 and Not 20).
It should do that individually for each part type. That means I need a
for...Next loop here. But I don't want to consider each record as unique; in
other words the "Type" X should be considered as a group and it's same for
Type Y. So to achieve this, I used the following code:

Do While Not rs.EOF
For Each rs!Type In rs
UPDATE Sheet1
SET Total = PerCent * (Quantity - IIF([Number]>0,p[Number],0))
Next rs!Type

But it's not working for some reason. Do you see why or perhaps a better way
to achieve this? Thanks, once again, for your help!

 

[Alex Dybenko]

Hi,
well, not sure I understand the logic, but anyway - in order to have your
code working it should look like:

Do While Not rs.EOF
CurrentProject.Connection.execute "UPDATE Sheet1 " & _
"SET Total = PerCent * (Quantity - IIF([Number]>0,[Number],0))"

rs.MoveNext
loop

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hi,

I cannot do that because it's little complicated than that. I will try to
explain the table in another way to make it clear. Let's say I need to get
100
parts of type X and I have decided to get 20% from supplier A; 50% from
supplier B and 30% from supplier C. So the total field, in this case,
would
be (100*20%) for Row 1; (100*50%) for Row 2; (100*30%) for Row 3. But in
some case rather than giving the supplier type in percentage, we may give
the
data
in Numerical quantity. For example, let's say, I need another 200 parts in
TYPE Y and I am going to get 20% from supplier A; 80% from supplier B and
also I have a special request here which says, out of those 200 parts I
need
to get 100 parts from Supplier C(this is the "Number" field...and this is
not
the actual name...actual name is SplRequest...I named it this way here for
convenience). That means, I will only get 20% of 100 parts from supplier A
and NOT 20% of 200 parts. So my total field, in this case, would be
((200-100)*20% for Row 1; ((200-100)*80%) for Row 2; and 100 parts for Row
3.
I have reproduced the table here again for convenience:

Type Quantity Supplier Percent Number Total
X 100 A 20% 20
X 100 B 50% 50
X 100 C 30% 30
Y 200 A 20% (200-100)*20%=20
Y 200 B 80% (200-100)*80%=80
Y 200 C 100 100

So my point is the update command would work fine if I don't have the
"Type"
field. But, with the Type field, it's like each "Type" acts as a group. In
other words, if I just say Total=PerCent * (Quantity -
IIF([Number]>0,p[Number],0)), it would apply that to all the records which
is
not the desired case as you can see in the table(If it had been that way,
field corresponding to Type Y and supplier A would have been 40 and Not
20).
It should do that individually for each part type. That means I need a
for...Next loop here. But I don't want to consider each record as unique;
in
other words the "Type" X should be considered as a group and it's same for
Type Y. So to achieve this, I used the following code:

Do While Not rs.EOF
For Each rs!Type In rs
UPDATE Sheet1
SET Total = PerCent * (Quantity - IIF([Number]>0,p[Number],0))
Next rs!Type

But it's not working for some reason. Do you see why or perhaps a better
way
to achieve this? Thanks, once again, for your help!

Hi,
you can just make a query with following SQL:

Select *, iif(number=0, quantity * percent, (quantity - number) *
percent)
as Total
From sheet1

or even:

Select *, (quantity - number) * percent as Total
From sheet1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com