Hello,
I have query that list all the posible sums of three numbers in a table. the table only has 70 records, but when i runs the query returns about 275000 records
'step 1 (the query that returns the 275000 records)
SELECT DISTINCT a.valor + b.valor + c.valor AS sum_of_3_numbers, a.valor AS aValue, b.valor AS bValue, c.valor AS cValue FROM tblSBNC_P AS a, tblSBNC_P AS b, tblSBNC_P AS c WHERE a.Punt = 0 AND b.Punt = 0 AND c.Punt = 0 AND a.ID <> b.ID AND a.ID <> c.ID AND b.ID <> c.ID
step 2
after this i do a loop where I try to match "sum_of_3_numbers" for an exact match with one record from another table. step 1 does not take long time to process but the loop does, it might hang for 5 minutes before it reaches the end of the recordset
how can i reduce the time the code executes? another query instead of the loop? or is there a way to limit the amount of values the loop tries to match?
I have query that list all the posible sums of three numbers in a table. the table only has 70 records, but when i runs the query returns about 275000 records
'step 1 (the query that returns the 275000 records)
SELECT DISTINCT a.valor + b.valor + c.valor AS sum_of_3_numbers, a.valor AS aValue, b.valor AS bValue, c.valor AS cValue FROM tblSBNC_P AS a, tblSBNC_P AS b, tblSBNC_P AS c WHERE a.Punt = 0 AND b.Punt = 0 AND c.Punt = 0 AND a.ID <> b.ID AND a.ID <> c.ID AND b.ID <> c.ID
step 2
after this i do a loop where I try to match "sum_of_3_numbers" for an exact match with one record from another table. step 1 does not take long time to process but the loop does, it might hang for 5 minutes before it reaches the end of the recordset
Code:
Do While Not rsSums.EOF
sumValueStr = rsSums!sum_of_3_numbers
' Replace the dots with commas to handle the number format correctly
sumValueStr = Replace(sumValueStr, ",", ".")
' Open recordset for tblSCNB_P to find matching record for sumValue
Set rsSCNB = db.OpenRecordset("SELECT * FROM tblSCNB_P WHERE valor = " & sumValueStr & " AND punt = 0", dbOpenDynaset)
' Check if a matching record is found for sumValue
If Not rsSCNB.EOF Then
aValue = rsSums!aValue
bValue = rsSums!bValue
cValue = rsSums!cValue
' Update cor field in tblSBNC_P for aValue, bValue, and cValue
db.Execute "UPDATE tblSBNC_P SET cor =" & bc & " WHERE valor IN (" & Replace(aValue, ",", ".") & "," & Replace(bValue, ",", ".") & "," & Replace(cValue, ",", ".") & ")"
' Update cor field in tblSCNB_P for -sumValue
db.Execute "UPDATE tblSCNB_P SET cor =" & bc & " WHERE valor = " & Replace(sumValueStr, ",", ".")
Exit Sub
End If
rsSCNB.Close
rsSums.MoveNext
Loop
how can i reduce the time the code executes? another query instead of the loop? or is there a way to limit the amount of values the loop tries to match?