Newbie requesting help

[j345ve99]

I have a table that contains the following information

Table 1

IDNUm Msg MSg_Type
1zzz test1 A
1zzz test2 B
1fff test1 A
1fff test3 A
2hhh test1 C
2hhh test2 C


I would like to write a query that will allow me to loop through table 1 and insert
the value into table 2 using the format listed below. I am very desperate need of this solution and I am also coding with VBA. Thank you in advance for any help or suggestion.

Table 2


IDNUM MSG1 MSG2 MSG_type1 MSG_Type2
1zzz test1 test2 A B
1fff test1 test3 A A
2hhh test1 test2 C C

 

[fneily]

This has been answered numerous times. Just search on concatenate.
Or see:
thread701-1254471

 

[j345ve99]

Hi Fneily

I am not trying to concatenate those values. I am sorry, if I am not clear. I have read the value from Table 1 into a recordset. Now, I would like to insert those values into table 2 as I am looping through the recordset.

For example, I am reading the first record in table 1 and I insert into Table 2 ('IDNUM', 'MSG1', 'MSG_Type1') values ('1zzz','test1','A'). Now I loop and read the second record, the second record IDNUM is 'Izzz'; now I want to go back to table 2 and edit the record to insert into ('Msg2', Msg_Type2) with value ('test2', ''B') where 'Izzz' is the same.

Then I go and read the next record and go through the same process.

I hope this makes sense.

 

[dhookom]

Try this code which requires a reference to the DAO library

Sub Table1ToTable2()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    db.Execute "DELETE * FROM Table2", dbFailOnError
    Set rs1 = db.OpenRecordset("SELECT IDNum, Msg, Msg_Type FROM table1 ORDER BY IDNum, Msg")
    Set rs2 = db.OpenRecordset("SELECT IDNum, Msg1, Msg2 ,Msg_Type1,Msg_Type2 FROM table2")
    With rs1
        Do Until .EOF
            rs2.FindFirst "IDNum ='" & .Fields("IDNum") & "'"
            If rs2.NoMatch Then
                rs2.AddNew
                rs2!IDNum = .Fields("IDNum")
                rs2!Msg1 = .Fields("Msg")
                rs2!Msg_Type1 = .Fields("Msg_Type")
                rs2.Update
             Else
                rs2.Edit
                rs2!Msg2 = .Fields("Msg")
                rs2!Msg_Type2 = .Fields("Msg_Type")
                rs2.Update
            End If
            .MoveNext
        Loop
        .Close
    End With
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[j345ve99]

Thank you dhookom, that actually worked. Your awesome !!!!!!

 

[PHV]

And a pure SQL attempt:
INSERT INTO [Table 2] (IDNUM, MSG1, MSG2, MSG_type1, MSG_Type2)
SELECT A.IDNUm, A.Msg, B.Msg, A.MSg_Type, B.MSg_Type
FROM [Table 1] AS A INNER JOIN [Table 1] AS B ON A.IDNUm = B.IDNUm
WHERE A.Msg < B.Msg

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886