comma separated numbers

R

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?
 
J

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?
Click to expand...

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]
 
Top