arrays value overwritten wrongly in loop

G

Guest

Dear all,

I m trying to calculate the total of all products with a Fact of FN using the following formula
SUMPRODUCT(Qty of Jan-CY * FN of Jan-CY) / total Qty of Jan-CY for all prdts with same Model code with the following codes. The value for the arrays qty, sls and get overwritten each time RS_q1 (as below is looped). Therefore the actual values I m looking for cannot be obtained. Anyone can tell me what's wrong with my codes? Thanks in advance!

looking at the codes below and sample query or data below, this is wat i want
when model = ABC003 and Region = AP for Oct-CY
formula for ttlfn is ttlfn = (NZ's qty * NZ's fn) + (AU's qty * AU's fn) = (0 * 0) + (78 * 20.3101403) = 1584.1909434
BUT currently, my codes is giving me 412.50179900568409 [(0 * 0) + (20.3101403 * 20.3101403)] as the value of quantity for AU has been overwritten from "78" to "20.3101403" in the loop.

AND the expected results for the truncated sample of NSP should be:
Region Fact Model Jan-CY Feb-CY Oct-CY Nov-CY Qtr4-NY
AP FNtotal ABC003 0 0 1584.1909434 1209.765989 0

Sample of distinctModel query
Model Region
ABC005 Others
ABC006 Others
ABC100 AP
ABC003 AP
ABC004 AP
ABC005 AP

Truncated sample of NSP table
Country Region Fact Prdt_code Model Jan-CY Feb-CY Oct-CY Nov-CY Qtr4-NY
NZ AP Sls ABC003/AA ABC003 0 0 0 0 0
AU AP Sls ABC003/AA ABC003 0 0 6.29259111 4.80533154 0
AU AP Qty ABC003/AA ABC003 0 0 78 55 0
NZ AP Qty ABC003/AA ABC003 0 0 0 0 0
AU AP FN ABC003/AA ABC003 0 0 20.3101403 21.99574525 0
NZ AP FN ABC003/AA ABC003 0 0 0 0 0

Sample of codes

Sub NSP_RPT_Click()
Dim qty, fn, sls, ttlqty, ttlfn, ttlsls, mth, rfn
Dim select_str As String, model as String, reg as String

'retrieve data by plan type one at a time for calculating total by plan type
Set RS_q = DB.OpenRecordset("DistinctModel")

RS_q.MoveFirst
Debug.Print "Results" & vbCrLf & "---"
Do Until RS_q.EOF
For Each FLD In RS_q.Fields
Debug.Print FLD.Value,
Next
Debug.Print

pType = RS_q.Fields("Model")
reg = RS_q.Fields("Region")

'initialise the array storing the calculated fields containing the totals for the region
qty = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
sls = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
fn = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

ttlqty = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
ttlsls = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
ttlfn = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

rfn = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

select_str = "Select [Region], [Prdt_code], [Model], [Fact], [Jan-CY], [Feb-CY], [Mar-CY], [Apr-CY], [May-CY], [Jun-CY], [Jul-CY], [Aug-CY], [Sep-CY], [Oct-CY], [Nov-CY], [Dec-CY], [Jan-NY], [Feb-NY], [Mar-NY], [Apr-NY], [May-NY], [Jun-NY], [Jul-NY], [Aug-NY], [Sep-NY], [Oct-NY], [Nov-NY], [Dec-NY], [Qtr1-CY], [Qtr2-CY], [Qtr3-CY], [Qtr4-CY], [Qtr1-NY], [Qtr2-NY], [Qtr3-NY], [Qtr4-NY] FROM NSP WHERE [Model] = '" & model & "' AND Region = '" & reg & "'"
Set RS_q1 = CurrentDb.OpenRecordset(select_str)

'assigning fields extracted from recordset RS_q1 into variables
RS_q1.MoveFirst
Debug.Print "Results" & vbCrLf & "---"
Do Until RS_q1.EOF
For Each FLD In RS_q1.Fields
Debug.Print FLD.Value,
Next
Debug.Print

fact = RS_q1.Fields("Fact")

If fact = "Qty" Then
qty = Array(RS_q1.Fields("Jan-CY"), RS_q1.Fields("Feb-CY"), RS_q1.Fields("Mar-CY"), RS_q1.Fields("Apr-CY"), RS_q1.Fields("May-CY"), RS_q1.Fields("Jun-CY"), RS_q1.Fields("Jul-CY"), RS_q1.Fields("Aug-CY"), RS_q1.Fields("Sep-CY"), RS_q1.Fields("Oct-CY"), RS_q1.Fields("Nov-CY"), RS_q1.Fields("Dec-CY"), RS_q1.Fields("Jan-NY"), RS_q1.Fields("Feb-NY"), RS_q1.Fields("Mar-NY"), RS_q1.Fields("Apr-NY"), RS_q1.Fields("May-NY"), RS_q1.Fields("Jun-NY"), RS_q1.Fields("Jul-NY"), RS_q1.Fields("Aug-NY"), RS_q1.Fields("Sep-NY"), RS_q1.Fields("Oct-NY"), RS_q1.Fields("Nov-NY"), RS_q1.Fields("Dec-NY"), RS_q1.Fields("Qtr1-CY"), RS_q1.Fields("Qtr2-CY"), RS_q1.Fields("Qtr3-CY"), RS_q1.Fields("Qtr4-CY"), RS_q1.Fields("Qtr1-NY"), RS_q1.Fields("Qtr2-NY"), RS_q1.Fields("Qtr3-NY"), RS_q1.Fields("Qtr4-NY"))
For K = 0 To UBound(qty)
ttlqty(K) = ttlqty(K) + qty(K)
Next K
ElseIf fact = "Sls" Then
sls = Array(RS_q1.Fields("Jan-CY"), RS_q1.Fields("Feb-CY"), RS_q1.Fields("Mar-CY"), RS_q1.Fields("Apr-CY"), RS_q1.Fields("May-CY"), RS_q1.Fields("Jun-CY"), RS_q1.Fields("Jul-CY"), RS_q1.Fields("Aug-CY"), RS_q1.Fields("Sep-CY"), RS_q1.Fields("Oct-CY"), RS_q1.Fields("Nov-CY"), RS_q1.Fields("Dec-CY"), RS_q1.Fields("Jan-NY"), RS_q1.Fields("Feb-NY"), RS_q1.Fields("Mar-NY"), RS_q1.Fields("Apr-NY"), RS_q1.Fields("May-NY"), RS_q1.Fields("Jun-NY"), RS_q1.Fields("Jul-NY"), RS_q1.Fields("Aug-NY"), RS_q1.Fields("Sep-NY"), RS_q1.Fields("Oct-NY"), RS_q1.Fields("Nov-NY"), RS_q1.Fields("Dec-NY"), RS_q1.Fields("Qtr1-CY"), RS_q1.Fields("Qtr2-CY"), RS_q1.Fields("Qtr3-CY"), RS_q1.Fields("Qtr4-CY"), RS_q1.Fields("Qtr1-NY"), RS_q1.Fields("Qtr2-NY"), RS_q1.Fields("Qtr3-NY"), RS_q1.Fields("Qtr4-NY"))
For K = 0 To UBound(sls)
ttlsls(K) = ttlsls(K) + sls(K)
Next K
ElseIf fact = "FN" Then
fn = Array(RS_q1.Fields("Jan-CY"), RS_q1.Fields("Feb-CY"), RS_q1.Fields("Mar-CY"), RS_q1.Fields("Apr-CY"), RS_q1.Fields("May-CY"), RS_q1.Fields("Jun-CY"), RS_q1.Fields("Jul-CY"), RS_q1.Fields("Aug-CY"), RS_q1.Fields("Sep-CY"), RS_q1.Fields("Oct-CY"), RS_q1.Fields("Nov-CY"), RS_q1.Fields("Dec-CY"), RS_q1.Fields("Jan-NY"), RS_q1.Fields("Feb-NY"), RS_q1.Fields("Mar-NY"), RS_q1.Fields("Apr-NY"), RS_q1.Fields("May-NY"), RS_q1.Fields("Jun-NY"), RS_q1.Fields("Jul-NY"), RS_q1.Fields("Aug-NY"), RS_q1.Fields("Sep-NY"), RS_q1.Fields("Oct-NY"), RS_q1.Fields("Nov-NY"), RS_q1.Fields("Dec-NY"), RS_q1.Fields("Qtr1-CY"), RS_q1.Fields("Qtr2-CY"), RS_q1.Fields("Qtr3-CY"), RS_q1.Fields("Qtr4-CY"), RS_q1.Fields("Qtr1-NY"), RS_q1.Fields("Qtr2-NY"), RS_q1.Fields("Qtr3-NY"), RS_q1.Fields("Qtr4-NY"))
For K = 0 To UBound(fn)
If pType = "HDD100" Then
MsgBox reg & pType & " " & mth(K) & " qty: " & qty(K)
End If
Next K
End If

ttlfn(K) = ttlfn(K) + (fn(K) * qty(K))

RS_q1.MoveNext
Loop

RS_q.MoveNext
Loop

'clear the recordset
RS_q1.Close
Set RS_q = Nothing

'clear the db object
Set DB = Nothing

'enable the warnings when executing an SQL DDL command
DoCmd.SetWarnings True

End Sub

b.regards
yann
 
B

Bas Cost Budde

yann said:
Dear all,

I m trying to calculate the total of all products with a Fact of FN using the following formula
SUMPRODUCT(Qty of Jan-CY * FN of Jan-CY) / total Qty of Jan-CY for all prdts with same Model code with the following codes. The value for the arrays qty, sls and get overwritten each time RS_q1 (as below is looped). Therefore the actual values I m looking for cannot be obtained. Anyone can tell me what's wrong with my codes? Thanks in advance!


AND the expected results for the truncated sample of NSP should be:
Region Fact Model Jan-CY Feb-CY Oct-CY Nov-CY Qtr4-NY
AP FNtotal ABC003 0 0 1584.1909434 1209.765989 0
Click to expand...

sumproduct? Array(0,0,0)? You are using Access, right, not Excel and/or
javascript?

Try normalizing a bit (so you have a record for each month) and then
create a Totals query.
 
G

Guest

Hi Bas

Yes, I m using access, I am not actually using sumproduct in Access just trying to say that the calculation I need to make is actually a sumproduct if my data is in excel

when u mention a record for each month, do u mean modify the NSP table to be something like this
Country Region Prdt_code Model Mth Sls Qty F
AU AP ABC003/AA ABC003 Jan-CY 0 0
AU AP ABC003/AA ABC003 Feb-CY 0 0
AU AP ABC003/AA ABC003 Oct-CY 6.29259111 78 20.310140
AU AP ABC003/AA ABC003 Nov-CY 4.80533154 55 21.9957452
AU AP ABC003/AA ABC003 Qtr4-NY 0 0
NZ AP ABC003/AA ABC003 Jan-CY 0 0
NZ AP ABC003/AA ABC003 Feb-CY 0 0
NZ AP ABC003/AA ABC003 Oct-CY 0 0
NZ AP ABC003/AA ABC003 Nov-CY 0 0
NZ AP ABC003/AA ABC003 Qtr4-NY 0 0

Thanks
yann
 
B

Bas Cost Budde

yann said:
Hi Bas,

Yes, I m using access, I am not actually using sumproduct in Access just trying to say that the calculation I need to make is actually a sumproduct if my data is in excel.

when u mention a record for each month, do u mean modify the NSP table to be something like this?
Country Region Prdt_code Model Mth Sls Qty FN
AU AP ABC003/AA ABC003 Jan-CY 0 0 0
AU AP ABC003/AA ABC003 Feb-CY 0 0 0
Click to expand...

Yes. Certainly. Now how do you calculate that FN from your original
post? You can then apply the calculation and get at a total for every
"date" by using a totals query; to display the totals of ?one year? I
guess, include the year in the table, create a crosstab query based on
the totals query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

YTD % over 12 accumulating figures 1
help with formula to calculate what column to use 1
Pivot table 3
How To Name Columns With Code 6
Copy from sheet1 and sheet2 and append to sheet3 in different format 8
Can a report automatically update from a crosstab query? 2
Need help with SQL Statement in MS Access 2003 Chart 11
Nested IF Function help 3

Top