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.

[TABLE="width: 278"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]MSG_SeqNo[/TD]
[TD] AddJobId[/TD]
[TD] job_ID[/TD]
[/TR]
[TR]
[TD]20160301004811982451[/TD]
[TD] [/TD]
[TD] 1216[/TD]
[/TR]
[TR]
[TD]20160301004812593237[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004812661445[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004812839436[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004824828647[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004900825884[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004921093556[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004921326065[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004938098232[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301004939326558[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301005011097547[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301005015327326[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301005025826370[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301005025866153[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20160301005026343194[/TD]
[TD] [/TD]
[TD] 1216[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.