D
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
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