Joining Fields (1 Viewer)

[Welstead]

Hi Guys,

I have a problem which I cannot figure out.

I have a table which has an ID field and also a field with data. The ID field is 'not' unique.

I need to join all of the data fields which have the same ID field into one field. So it would be ID; Data 1, Data 2...

Any ideas? I would appreciate any help you could offer!

 

[KenHigg]

I think post 7 here has an example of one way to do this:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

 

[Welstead]

Hi Ken,

Thanks for the link to the DB. I'm trying to implement the solution into my DB. However I'm getting the following error "Syntax error in Union Query" It occurs at RS1.open

I've used the same setup as in the example. I created a temp table with no data but the same fields and field types.

Any suggestions?

Thanks

 

[KenHigg]

Paste your code along with your table and filed names.

 

[Welstead]

My tables are (LEV) MEB Overview I which has the data
and (LEV) MEB Overview II, which has no data

Code is:
Public Sub s_runMe()
Dim cn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset


rs1.Open "(LEV) MEB Overview I", cn, adOpenDynamic, adLockOptimistic
rs2.Open "(LEV) MEB Overview II", cn, adOpenDynamic, adLockOptimistic

rs1.MoveFirst
rs2.AddNew
rs2![personnel no] = rs1![personnel no]
rs2![period end] = rs1![period end]
rs1.MoveNext


Do While Not rs1.EOF
If rs1![personnel no] = rs2![personnel no] Then
rs2![period end] = rs2![period end] & ", " & rs1![period end]
Else
rs2.AddNew
rs2![personnel no] = rs1![personnel no]
rs2![period end] = rs1![period end]
End If

rs1.MoveNext

Loop

rs1.Close

MsgBox "Done!"

End Sub

 

[KenHigg]

A quick stab would be you need to bracket the table name as you've used a bad naming convention in the name by including parens and spaces. Maybe something like the following:

rs1.Open "[(LEV) MEB Overview I]", cn, adOpenDynamic, adLockOptimistic


???

 

[Welstead]

That worked, so simple! Obvious I'm not a programmer!

 

[KenHigg]

Cool - Glad you got it to work