Anyone got a quick solution for this?

[REM7600]

I want to convert the the tables below(From this, to this). I'm somewhat
proficient with VBA if that's necessary.

Thanks

Travis

FROM THIS

F1 F2
12345 VA7X
12345 40-5
12345 M5
12345 CXT1
12346 50-3
12347 VM8X
12347 VM25C



TO THIS

F1 F2 F3 F4 F5
12345 VA7X 40-5 M5 CXT1
12346 50-3
12347 VM8X
12347 VM25C

 

[Guest]

I think you messed up your example. If not then I don't see the pattern.
I'm assuming that the 'vm25c' value should appear in the F3 column in the
first '12347' row. If my assumption is correct then here's a procedure that
will convert your data. Just be sure that the new table accepting the
converted data has at least as many fields as the maximum number of records
assigned to a unique 'F1' value in your original table. I used DAO, so your
project will need to reference that library for the code to work. Good luck
Travis.

Dim dbs As DAO.Database
Dim rstSort As DAO.Recordset
Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Set dbs = CurrentDb
strSQL = "SELECT DISTINCT [F1] FROM [Table1]"
Set rstSort = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set rstFrom = dbs.OpenRecordset("Table1", dbOpenSnapshot)
Set rstTo = dbs.OpenRecordset("Table2", dbOpenDynaset)
Do Until rstSort.EOF
strSQL = "[F1]='" & rstSort("F1") & "'"
rstFrom.FindFirst strSQL
i = 0
rstTo.AddNew
rstTo.Fields(i) = rstFrom.Fields(0)
Do Until rstFrom.NoMatch
i = i + 1
rstTo.Fields(i) = rstFrom.Fields(1)
rstFrom.FindNext strSQL
Loop
rstTo.Update
rstSort.MoveNext
Loop
End Sub

 

[REM7600]

affirmative... Didn't proof it hard nuf eh?

Thanks, I'll try it!

TR

 

[REM7600]

elwin your example works. Timeliness might be an issue as the working
dataset is very large. But a big thanks for the help!

Travis