Convert list data to table

[ClifCamp]

Employee ID Job Title Effective Date
------------------------------------------
12345 Mgr III 2/15/2006
12345 Mgr II 3/1/2005
12345 Engineer III 2/28/2004
22741 Admin III 2/7/2006
22741 Admin II 4/15/2005
22741 Admin I 5/12/2004

I want to have one row in a table for each employee that looks like this:

EmplID Prior1Job Prior1Date Prior2Job Prior2Date,etc
12345 Mgr III 2/15/2006 Mgr II 3/1/2005
22741 Admin III 2/7/2006 Admin II 4/5/2005

How can I convert this list data into a table as above using VBA?

Thanks in advance for any help

 

[PaulF]

this example uses DAO, and calls the original table "tblEmpJobTitle" and the new table "tblPrior"



Dim db As DAO.Database, rstJob As DAO.Recordset, rstPrior
Dim strID As String, intX As Integer
Set db = CurrentDb
Set rstJob = db.OpenRecordset("Select * From tblEmpJobTitle Order By [Employee ID], [Effective Date] desc")
Set rstPrior = db.OpenRecordset("tblPrior", dbOpenDynaset)
With rstJob
.MoveFirst
Do While Not .EOF
With rstPrior
If rstJob![Employee ID] <> strID Then
.AddNew
rstPrior![Employee ID] = rstJob![Employee ID]
strID = rstJob![Employee ID]
intX = 1
Else
.FindFirst "[Employee ID] = '" & strID & "'"
.Edit
intX = intX + 1
End If
rstPrior("Prior" & CStr(intX) & "Job") = rstJob![Job Title]
rstPrior("Prior" & CStr(intX) & "Date") = rstJob![Effective Date]
rstPrior.Update
End With
.MoveNext
Loop
End With
MsgBox "Done:"
Set db = Nothing
Set rstJob = Nothing
Set rstPrior = Nothing


HTH
PaulF