comma separated numbers

[rce]

I have a database with 2 tables I need to join. Table1 has a text field
with numbers --
Example: This would be a single record.
1,2,3

Table2 has a number field I need to join to --
Example: This would be three records
1
2
3


How can I get the text field in Table1 to be separated into individual
records like Table2 so I can join them?
Can it be done in a query?

 

[RobFMS]

Dim dbs as DAO.Database
Dim rst as DAO.RecordSet
Dim strTemp as String

Set dbs = CurrentDB
Set rst = dbs.OpenRecordSet("Select <FieldX> From Table2")

If Not (rst.bof and rst.eof) Then

Do While Not rst.eof
strTemp = strTemp & rst("<FieldX>") & ","
rst.MoveNext
Loop

strTemp = Left(strTemp, Len(strTemp)-1)

End If


At this point, "strTemp" will contain the string of numbers.

HTH

Rob Mastrostefano

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[John Vinson]

I have a database with 2 tables I need to join. Table1 has a text field
with numbers --
Example: This would be a single record.
1,2,3

Table2 has a number field I need to join to --
Example: This would be three records
1
2
3


How can I get the text field in Table1 to be separated into individual
records like Table2 so I can join them?
Can it be done in a query?

You'll need some VBA code and probably another table. Let's say Table1
has a primary key Table1ID and that this field is named Nums. I'd
create a new table, Table1Numbers, with two fields - Table1ID and Num.
You could then run this code (or something like it):

Public Sub Atomic()
Dim db As DAO.Database
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim Nums() As Long
Dim iPos As Integer
Set db = CurrentDb
Set rsIn = db.OpenRecordset("Table1", dbOpenSnapshot)
Set rsOut = db.OpenRecordset("Table1Numbers", dbOpenDynaset)
Do Until rsIn.EOF
Nums = Split(rsIn!Num) ' move the string into an array
For iPos = 0 to UBound(Nums) 'loop through the array
rsOut.AddNew ' create a new record
rsOut!TableID = rsIn!TableID
rsOut!Num = Nums(iPos)
rsOut.Update
Next iPos
rsIn.MoveNext
Loop
End Sub

You'll then be able to join Table2 to Table1Numbers to get your link.

John W. Vinson[MVP]