joinig two tables

jsirico · #1 (**permalink**) 01-25-12, 09:44

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?

Sinndho · #2 (**permalink**) 01-25-12, 10:49

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.

jsirico · #3 (**permalink**) 01-25-12, 10:52

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

jsirico · #4 (**permalink**) 01-25-12, 11:08

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

Sinndho · #5 (**permalink**) 01-25-12, 12:07

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

jsirico · #6 (**permalink**) 01-25-12, 12:47

problem2 : the codeMAD filed in the output is empty!!!! why?????
The code works well without problems but the codeMAD fild will be always empty!!!!

Sinndho · #7 (**permalink**) 01-25-12, 13: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

jsirico · #8 (**permalink**) 01-26-12, 04:35

something is wrong, the vba code seem to be the right one but still it doesn't produce any result!!!! whats the matter????

Sinndho · #9 (**permalink**) 01-26-12, 04:44

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;

jsirico · #10 (**permalink**) 01-26-12, 05:00

4 fields empty!!!!!

jsirico · #11 (**permalink**) 01-26-12, 05:29

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

Sinndho · #12 (**permalink**) 01-26-12, 05:59

No problem, you're welcome!

Internet Services

Web Development

Digital Marketing

Consumer Tech