Help with DAO Recordset

[GPUser05]

I am opening query as recordset and looping through the records, doing calculation with the some of the data and appending the record to another table. These are the product quantity, cost, sales price and profit margin records for half month say 1/1 through 1/15.

Generally, there is only one record for each product type but sometime due to salesprice change there can be two records for one product. How can I set up the inner loop so that it will loop within the outer loop and do the calculation until the product ID is same and exit the inner loop when product ID changes.

Sample Data for the query
ProductID Qty Cost SalesPrice TotSalesPrice
A 5 10 12 60
A 8 10 13 104
B 10 12 15 150
B 10 12 14 140

I cannot total by product ID in the query as I need to append the last salesprice in another table.

There are much more fields but have shown the fields I need for inner loop.

Thanks for your time and help

 

[dhookom]

Please provide your current code so we don't have to start from scratch.

Duane
Hook'D on Access
MS Access MVP

 

[GPUser05]

Set rsCommRec = db.OpenRecordset("qry_comm_Data", dbopensnapshot)

Do Unitl rsCommRec.EOF

GoSub NewVars ' this sub routine set all variables to zero before the next product ID


If myPreProdID <> myProdID Then

myProdID = rsCommRec("ProductID")

myInvAmt = myInvAmt + (rsCommRec("Qty")* rsCommRec("SalesPrice")

End If

If rsCommRec("Expense_type2") = "Travel" Then

myTrvelCost = rsCommRec("Exp2")* 157.33/2
myProdCost = rsCommRec("Cost")+ myTravelCost + Exp1 +Exp3 + Exp4

Else
myProdCost = rsCommRec("Cost") + Exp1 +Exp3 +Exp4

End If

myPMargin = myInvAmt - myProdCost
myPreProdID = myProdID
GoSub WriteRe 'sub routine to append data to another table

rsCommRec.MoveNext

Loop

The first four lines of If statements, I would like to put in a loop. It is skipping the record when Product ID is changed second time and not doing the calculation.

Thanks for your prompt response.

 

[MajP]

This should be done with a pure SQL solution. I can not see any need for looping.

I cannot total by product ID in the query as I need to append the last salesprice in another table

Append query?

 

[dhookom]

There is no way that code would even run/compile. The code provides no indication if something like "myProdID" is a memory variable or reference to a value on a form.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

This is all of the code? you reference variables without declaring them? Post all of the code ... or if this all then this needs work

let us know and we can help

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MajP]

But again why? Clearly you can get the invmat, prodCost and trvlCost through sql

Select
qty * SalesPrice as invAmt,
iif([Expense_Type2] = "Travel",[Exp2] * 157.33/2,0) as trvlCst,
iif([Expense_Type2] = "Travel", [Cost] + [Exp2] * 157.33/2 + [Exp1] + [Exp3] + [Exp4],[Cost] + [Exp1] +[Exp3] +[Exp4]) as prodCost,


Now there sounds like these need to be then grouped by product ID and summed. Then if necessary a subquery to return the last sales price for each group (if I understand correctly). If it was me and I am not very good at sql, I would first build that query to ensure I get the right answers. Then build an append query from that query. I still do not see any need for dao, unless it is an academic exercise.

I personally do not use iifs in queries, and would build custom functions to return prodcost and tvlcost. But basically the same idea.

 

[GPUser05]

Thanks for your responses. All variables are declared even the myProdID. Sorry for the delay in reply.

MajP,

I would prefer not to use the IIfs in queries but I have never build custom function. Would you be able to give one example?

Thank you for your time.

 

[MajP]

replaces iif([Expense_Type2] = "Travel",[Exp2] * 157.33/2,0) as trvlCst

Use in a query something like.
Select
getTrvlCost([Expense_Type2],[Exp2]) as trvlCost

Public Function getTrvlCost(ExpenseType As Variant, Exp2 As Variant) As Currency
  'Check no nulls passed in
  If Not IsNull(ExpenseType) And IsNumeric(Exp2) Then
    If ExpenseType = "Travel" Then
      getTrvlCost = Exp2 * 157.33 / 2
    End If
  End If
End Function

Here is the advantage:
I can test this prior to putting in a query, by passing it literal values
Much easier to error check and handle null values
I can use it anywhere. Other queries, calculated controls.
Easier to read especially with nested iifs
Easier to debug
More efficient.
Way easier to reuse.

 

[MajP]

Here is how I can test it in the immediate window
'Pass good values
X="Travel"
Y=123
?getTrvlCost(x,y)
9675.795

'Pass a non travel cost
X="Not Travel"
Y="123"
?getTrvlCost(x,y)
0

'Test null handling
X = null
Y = null
?getTrvlCost(X,Y)
0

 

[GPUser05]

Thanks MajP, I will try to use the function.

Thanks for your time.

 

[MajP]

The function was only an example. I hope you use the concept in your database programming.