Moving records to new table

[Joseph Balzamo]

I have an existing table with one field and many records,
I need to move all of the records from my existing table
into a new table but I want to be able to place the first
4 records of my existing table into 4 seperate fields of
my new table and then place the next 4 records of my
existing table into the second set of records in my new
table.

Example...:

Existing Table

Field 1
---------
record1
record2
record3
record4
record5
record6
record7
record8


New Table

Field1 Field2 Field3 Field4
-----------------------------------
record1 record2 record3 record4
record5 record6 record7 record8

How can I do this? any help would be appriciated.

Thank you

 

[Roger]

I don't know how to do this in Access, but I'm sure a nice macro in Excel could do the trick.

 

[Guest]

-----Original Message-----
I don't know how to do this in Access, but I'm sure a

nice macro in Excel could do the trick.

.

Hi Roger,

I imported the table into excel and basically have the
same problem, one row of 2500 records. I poked around
looking for some way to seperate the records into 4
seperate fields but I just have no clue how to accomplish
it. If its not to much trouble can you maybe give me some
ideas on how to do this with a macro like you said.

Thanks Joe

 

[Tom Wickerath]

Hi Joe,

You can record a macro in Excel pretty easily (Tools > Macro > Record new macro). You
would let the macro record the actions for re-arranging the first four records. Then turn
off the macro recorder. You'd still have to add some logic to get it to "loop" through
all rows in your spreadsheet.

In Access, you can accomplish the same task by opening two recordsets. You use one to
read the data and the other one to write it to a new table. The code shown below assumes
a source table named tblOriginalData, with a single field named Field1 and a target table
named tblRearrangedData with four fields: Field1, Field2, Field3 and Field4.

Create a new module, and paste the code shown below into this module. Then, with the
cursor anywhere within the subroutine, click on
_________________________________________

Option Compare Database
Option Explicit

Sub RearrangeData()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strText As String
Dim i As Integer

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("tblOriginalData")
Set rs2 = db.OpenRecordset("tblRearrangedData")

rs1.MoveFirst

Do Until rs1.EOF
rs2.AddNew
For i = 1 To 4
strText = rs1("Field1")
rs2("Field" & i) = strText
rs1.MoveNext
Next i
rs2.Update
Loop

MsgBox "Records successfully rearranged", , "Operation finished"

ExitProc:
On Error Resume Next
rs1.Close
rs2.Close
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub



____________________________________________

Hi Roger,

I imported the table into excel and basically have the
same problem, one row of 2500 records. I poked around
looking for some way to seperate the records into 4
seperate fields but I just have no clue how to accomplish
it. If its not to much trouble can you maybe give me some
ideas on how to do this with a macro like you said.

Thanks Joe
____________________________________________


I don't know how to do this in Access, but I'm sure a nice macro in Excel could do the
trick.

 

[Joseph Balzamo]

Thanks Tom I will try this out later on today when I have
some more time.

Joe

-----Original Message-----
Hi Joe,

You can record a macro in Excel pretty easily (Tools >

Macro > Record new macro). You

would let the macro record the actions for re-arranging

the first four records. Then turn

off the macro recorder. You'd still have to add some

logic to get it to "loop" through

all rows in your spreadsheet.

In Access, you can accomplish the same task by opening

two recordsets. You use one to

read the data and the other one to write it to a new

table. The code shown below assumes

a source table named tblOriginalData, with a single

field named Field1 and a target table

named tblRearrangedData with four fields: Field1, Field2, Field3 and Field4.

Create a new module, and paste the code shown below into this module. Then, with the
cursor anywhere within the subroutine, click on
_________________________________________

Option Compare Database
Option Explicit

Sub RearrangeData()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strText As String
Dim i As Integer

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("tblOriginalData")
Set rs2 = db.OpenRecordset("tblRearrangedData")

rs1.MoveFirst

Do Until rs1.EOF
rs2.AddNew
For i = 1 To 4
strText = rs1("Field1")
rs2("Field" & i) = strText
rs1.MoveNext
Next i
rs2.Update
Loop

MsgBox "Records successfully rearranged", , "Operation finished"

ExitProc:
On Error Resume Next
rs1.Close
rs2.Close
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub



____________________________________________

Hi Roger,

I imported the table into excel and basically have the
same problem, one row of 2500 records. I poked around
looking for some way to seperate the records into 4
seperate fields but I just have no clue how to accomplish
it. If its not to much trouble can you maybe give me some
ideas on how to do this with a macro like you said.

Thanks Joe
____________________________________________


(e-mail address removed)...

I don't know how to do this in Access, but I'm sure a

nice macro in Excel could do the

 

[Joseph Balzamo]

It worked!!! Tom I can't thank you enough thank you,
thank you, thank you.

 

[Tom Wickerath]

Hi Joseph,

You're welcome!

A few points that I forgot to mention in my post. First, this method is not tolerant of
nulls in the source data. So, if you have any missing values for records, such as:

record1
record2

record4
record5

you will get an "Error 94: Invalid use of Null". If this is the case, then you can either
delete the record with the missing value (although this might affect the proper order, by
all remaining records up one position) or you can replace the null with a zero length
string. To do this, you click into the cell with the null, hold down your shift key, and
enter two quotation marks (Shift ""). You could also base the first recordset, rs1, on a
query which includes a criteria to filter out nulls--this would have the same effect as
moving all remaining records up one position.

There are other approaches as well. One could have dimensioned an array and populated it
with a set of four values, and then used a loop to write these four values to a new table.

Tom
____________________________________


It worked!!! Tom I can't thank you enough thank you, thank you, thank you.