SQL Increment field name

[DanRoy]

I am counting the number of occurrences of a key field in one table and
storng the result in another table to form the basis for a status table of
completions. However, i am having problems in getting the SQL right.
my code:

' populate the table with values
With CurrentDb.OpenRecordset("Dashmaster", dbOpenDynaset)

Do Until .EOF ' To step through all records

If (Len(.Fields("Team code")) = 1) Then teamref = "0" &
..Fields("TEAM code") _
Else teamref = .Fields("TEAM code")
dashsow = .Fields("change number") & "-" & teamref

SQL = "Update Measurement_Plan " & _
" Set [measurement_plan].[Count] =
[measurement_Plan].[count] +1 " & _
" Where [Measurement_Plan].Key = '" & dashsow & "'"
Label30.Caption = SQL
List33.AddItem SQL
CurrentDb.Execute SQL, dbFailOnError

.MoveNext
Loop
End With

Can anyone tell me how the SQL code should be written such that I cna
increment Measurement_plan.count by 1 everytime I find a match to the current
key field. The value of Dashsow is equal to the value in key in the
receiving table.

Thanks

 

[DanRoy]

working code-
' populate the table with values
With CurrentDb.OpenRecordset("Dashmaster", dbOpenDynaset)

Do Until .EOF ' To step through all records

If (Len(.Fields("Team code")) = 1) Then teamref = "0" &
..Fields("TEAM code") _
Else teamref = .Fields("TEAM code")
dashsow = .Fields("change number") & "-" & teamref

SQL = "Update Measurement_Plan " & _
" Set [measurement_plan].[Cnt RAM BOE's] =
[measurement_plan].[Cnt RAM BOE's] +" & 1 & _
" Where [Measurement_Plan].Key = '" & dashsow & "'"
Label30.Caption = SQL
List33.AddItem SQL
CurrentDb.Execute SQL, dbFailOnError


.MoveNext
Loop
End With