I need your help again!!!!
this is the sitation:
I've two tables, the first is done like this
where the field "concat" is the result of concatenation of 6 fields and codeMAD is a short code which identifies the fields.
the table is composed by 419 records which are different from rows to rows
the second table
Has the follwonig structure:
The second table is composed by 918 record; but here the field concat is not univoc, in other words there severals repetitions of the same concatenated string.
What I need to do is to add the fields codeMad stored in the first tale to the second table using as reference the fileds concat;
if the value of the fields concat is the same for two records than they 'll have the same value of CodeMAD.
I've manaeged to do a left join selection but I'm not able to realize my goal, is there an easier way to obtain the same result?
This is a query. If you want to use it in a VBA module, use:
Dim strSQL As String
Const c_SQL = "UPDATE SecondTable SET SecondTable.codeMAD = DLookUp(/codeMAD/, /FirstTable/, /concat='/ & [SecondTable].[concat] & /'/);"
CurrentDb.Execute Replace(c_SQL, "/", Chr(34)), dbFailOnError
It worked when I tried (using a query), but I'm not sure to understand why or how, this is why I wrote "seems to work".
As an alternative, you can do it with a recordset in a loop:
Dim rst As DAO.Recordset
Dim strcodeMAD As String
Set rst = CurrentDb.OpenRecordset("SecondTable", dbOpenDynaset)
Do Until .EOF
strcodeMAD = Nz(DLookup("codeMAD", "FirstTable", "concat='" & !concat & "'"), "")
If Len(strcodeMAD) > 0 Then
!codeMAD = strcodeMAD
Set rst = Nothing