Query

[Waqas ali]

dear experts,

i have below table with msg_seqno ,addjobid and job_id. i want to update same job_id against each msg_seqno in AddJobId. how i have to write a query.

is it by correlated query or with simple update query? please write a query.

MSG_SeqNo	AddJobId	job_ID
20160301004811982451		1216
20160301004812593237
20160301004812661445
20160301004812839436
20160301004824828647
20160301004900825884
20160301004921093556
20160301004921326065
20160301004938098232
20160301004939326558
20160301005011097547
20160301005015327326
20160301005025826370
20160301005025866153
20160301005026343194		1216

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[Waqas ali]

dear Xenou,

i did it by using recordset as below. QjobId query is getting jobid , min and max msgseq no which is pass to variables and then pass to the update query.

Sub updateJobID()


Dim db As DAO.Database, qrStr As String, stDt As Variant, enDt As Variant
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs_sender As String, jbId As String


Set db = CurrentDb


Set rs = db.OpenRecordset("QjobId", dbOpenDynaset)


While Not rs.EOF
With rs


stDt = (.Fields("MinSeq"))
enDt = (.Fields("MaxSeq"))
rs_sender = .Fields("sender")
jbId = .Fields("job_Id")

'*******************************
DoCmd.SetWarnings False
qstr = "UPDATE Cls_Result SET Cls_Result.AddJobId = '" & jbId & "' WHERE " & _
"(Cls_Result.sender)='" & rs_sender & "' AND cvar(Cls_Result.MSG_SeqNo) Between " & (stDt) & " And " & (enDt) & ""
DoCmd.RunSQL qstr
DoCmd.SetWarnings True
'******************************
.MoveNext
End With
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[xenou]

That's fine. Using recordsets in code is also a popular way to tackle a problem such as this.