help with restructuring a form

[Guest]

Hello. I am trying to merge data from 2 different sources. In order to do
this, I need to first manipulate the data from one of the sources. The data
that I am provided looks like this (put into 3 columns):

93900 30303E Math
93900 40404E English
93900 50505E Science
94911 30304E Math II
94911 40405E English II
94911 50506E Science II

I need the data to look like this: (put into 7 columns)

93900 30303E Math 40404E English 50505E Science
94911 30304E Math II 40405E English II 50506E Science II

Anybody have any suggestions?

 

[Guest]

My data is coming from a table named Test. It has, the creatively named,
fields: field1 ( ID field ), field2 and field3.

I created an empty table named textx with fields: ID and 7 sets of fields (
field1 & field1a, field2 & field2a, etc )

The sub RUNALL does a query on the data field and creates a list of valid
IDs, it then runs eachs of these IDS through the merge_to_row procedure once,
appending them into the new table.


Hope it helps, if you have questions on anything ask away.




Sub RUNALL()
Dim db As Database
Dim rst As Recordset
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TEST.Field1 FROM TEST GROUP BY
TEST.Field1")
rst.MoveLast
rst.MoveFirst


For i = 1 To rst.RecordCount - 1
Call merge_to_row(rst!Field1)
rst.MoveNext
Next i

Set db = Nothing
End Sub

Sub merge_to_row(inID As String)
Dim db As Database
Dim rst As Recordset
Dim i As Integer
Dim SQL1 As String
Dim SQL2 As String

Call DoCmd.SetWarnings(False)
SQL1 = "INSERT INTO testx ( ID"
SQL2 = "SELECT '" & inID & "'"


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM [Test] WHERE [Field1] = '" &
inID & "'")
rst.MoveLast
rst.MoveFirst

If rst.RecordCount > 0 Then

For i = 1 To rst.RecordCount
SQL1 = SQL1 & ", field" & Trim(Str(i)) & ", field" & Trim(Str(i)) &
"a"
SQL2 = SQL2 & ", '" & rst!FIELD2 & "', '" & rst!FIELD3 & "'"
rst.MoveNext
Next i

End If

Call DoCmd.RunSQL(SQL1 & ") " & SQL2)

Set db = Nothing

End Sub

 

[Peter Hibbs]

Jim,

A Flex Grid Control will probably do what you want but it would
involve a bit of VBA coding. Have a look at -

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

for some examples.

Peter Hibbs.


On Thu, 25 Oct 2007 09:01:04 -0700, Jim Linnenburger <Jim