OK - here is some sample code for trying to transpose your data.
I will assume that the table holding the original data is named tblOld.
Create the new table that will hold the "transposed" data. I will assume
that the name of the table is tblNew and that the names of the fields will
be these:
fldName
fldAddress
fldCity
fldState
fldZipCode
(Note: Do not use Name as the name of a field. Name is a word with special
meaning to ACCESS, and using it as a field name may/will confuse ACCESS at
some point.)
Create a regular module (name it basTranspose) and put the following code in
it (warning: this code has not been fully tested; also, if the records in
the original table are not in the order anticipated by this code [a group of
name, address, zipcode, city, and state records; then this group repeats],
the code will fail):
Public Sub TransposeTableData()
Dim dbs As DAO.Database
Dim rstOrig As DAO.Recordset, rstNew As DAO.Recordset
Dim intCount As Integer
Set dbs = CurrentDb()
Set rstNew = dbs.OpenRecordset("tblNew", dbOpenDynaset, dbAppendOnly)
Set rstOrig = dbs.OpenRecordset("tblOld", dbOpenDynaset, dbReadOnly)
rstOrig.MoveFirst
Do While rstOrig.EOF = False
rstNew.AddNew
For intCount = 1 To 5
rstNew.Fields("fld" & rst.Fields(0)) = rst.Fields(1)
rstOld.MoveNext
Next intCount
rstNew.Update
rstOld.MoveNext
Loop
rstNew.Close
rstOld.Close
Set rstNew = Nothing
Set rstOld = Nothing
dbs.Close
Set dbs = Nothing
End Sub
Then run this code by clicking anywhere within the code while the Visual
Basic Editor is open, and clicking on the Run icon (right-pointing triangle
on toolbar). If an error occurs, VBE will display a "debug" window telling
you of the error.
