count first 4 columns

[Machiaveli]

Hello,

Could somebody help me adjust my code below. I would like to automatically insert the record of multiple tables to another table.

For each record, after the first four columns there should be an carriage return so that the records can be transformed.

Here's the code:
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim CC As Long
Dim fd As DAO.Field

Set rs = CurrentDb.OpenRecordset("Select * From table1")
Set rs1 = CurrentDb.OpenRecordset("Select * From tblLengths")

Do Until rs.EOF
On Error Resume Next
For Each fd In rs.Fields
With rs1
.AddNew
!FieldName = fd.Name
!Fieldvalue = fd.Value
!FieldLength = Len(fd.Value)
.Update
End With
'Debug.Print fd.Name & " : " & Len(fd.Value)
CC = CC + Len(fd.Value)
Next fd
rs.MoveNext
Loop

Set rs = Nothing
Set rs1 = Nothing

 

[Elysium]

You want to store the carriage return in the table? Could you provide some real sample data?

Randy

 

[Machiaveli]

Hi,

No i do not want to store the carriage return in the database.

I'll give you an example.

I have table where i have the following fields in datasheetview:

id | date | image | question1 | question2 | question..N

I would like to have it in this way:

id | date | image | questionnr | answer
1 01-01-2004 test.tif 1 1000 (this is the value of field question1)

As you can see the first 3 fields remains the same, but the records of those
3 fields should be inserted for each record of the question fields

How can i do this automatically, because there are more than 500 columns.

 

[Elysium]

Just to be sure I have the idea right, you want to normalize your data from this:

ID | Date | Image | Question 1 | Question 2 | ... Question 500

To:

ID | Date | Image | Q Num | Answer
1 01/01/2004 test.tif 1 1000
1 01/01/2004 test.tif 2 2000
1 01/01/2004 test.tif 3 2500
...
1 01/01/2004 test.tif 500 1001


Does this look right?

Regards,


Randy

 

[Machiaveli]

Hi Ely,

Yes that's basically it.

But the fieldnames must be under the field Q Num, like:

Q Num
Question 1
Question 2
etc..

However fieldnames may vary, so it is not always question1, question2, etc.

Can you help me with this?