Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2012
    Posts
    54

    Unanswered: joinig two tables

    Hi guys
    I need your help again!!!!
    this is the sitation:
    I've two tables, the first is done like this
    concat codeMAD
    107APPL... AA1
    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:
    concat otherfields
    107APPL... ........
    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?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure you can do that with a query in Access. The SQL is pretty straightforward and work in SQL Server:
    Code:
    UPDATE SecondTable 
    SET SecondTable.codeMAD = (SELECT TOP 1 FirstTable.codeMAD
                               FROM FirstTable
                               WHERE SecondTable.concat = FirstTable.concat
                              )
    ;
    However Access raises an error with a message saying that the query is not updatable.

    A mixed solution (SQL + VBA) seems to work:
    Code:
    UPDATE SecondTable 
    SET SecondTable.codeMAD = DLookUp("codeMAD", "FirstTable", "concat='" & [SecondTable].[concat] & "'");
    Or you can use a loop and a Recordset in a VBA procedure.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    54
    Thank you, I was near to the solution!!!! but I wasn't able to formulate it in the corret manner!!! I'll implements your suggestions immediatly I'll let you Know!!!
    Last edited by jsirico; 01-26-12 at 05:39.

  4. #4
    Join Date
    Jan 2012
    Posts
    54
    Problem:
    he says that SecondTable is a variable not defined!!!!! but it exists and it's written just like the way I've written in the set statament!!!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is a query. If you want to use it in a VBA module, use:
    Code:
    Sub UpdateSecondTable()
    
        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
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Jan 2012
    Posts
    54
    problem2 : the codeMAD filed in the output is empty!!!! why?????
    The code works well without problems but the codeMAD fild will be always empty!!!!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    Sub UpdateSecondTable()
    
        Dim rst As DAO.Recordset
        Dim strcodeMAD As String
        
        Set rst = CurrentDb.OpenRecordset("SecondTable", dbOpenDynaset)
        With rst
            Do Until .EOF
                strcodeMAD = Nz(DLookup("codeMAD", "FirstTable", "concat='" & !concat & "'"), "")
                If Len(strcodeMAD) > 0 Then
                    .Edit
                    !codeMAD = strcodeMAD
                    .Update
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Jan 2012
    Posts
    54
    something is wrong, the vba code seem to be the right one but still it doesn't produce any result!!!! whats the matter????

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Create a new query with this SQL statement and see what it returns:
    Code:
    SELECT FirstTable.codeMAD,
           FirstTable.concat, 
           SecondTable.concat,
           SecondTable.codeMAD
    FROM   FirstTable INNER JOIN 
           SecondTable ON FirstTable.concat = SecondTable.concat;
    Have a nice day!

  10. #10
    Join Date
    Jan 2012
    Posts
    54
    4 fields empty!!!!!

  11. #11
    Join Date
    Jan 2012
    Posts
    54
    ok, I've come to understand where is my problem, it's in the data, the fileds concat are different by desingn so it'll never be verifyed any equality!!!!!
    Sorry to have bothered you for nothing!!!!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem, you're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •