[AccessD] Multi value field contents

On 6 May 2006 at 11:41, Martin Reid wrote:

> I am sorting this myself but was wondering what you think about this and any
> non obvious solutionsyou may have?
> 
> 
> I have a field containing value1; value2; value3
> 
> Breaks all notions of relational design.
> 
> The table is a table project associated with many developers. In the Project
> Task Name Field the values are the developer names associated with the
> projects.
> 
> Is it possible in code to 
> 
> Break out the developer names and populate a table properly. 
> 
> This is Access 2007 and SQL Server 2005. The data is in SQL Server 2005
> 

Good old Split() once again :-)

Below is just the crux, it's up to you how you grab and write the data (ADO 
command objects, DAO recordset,  CurrentDB.Execute or whatever)

...'Fill recordset rs with Project table
....
While not rs.eof
strDevelopers() = split(rs!DeveloperNames)
For lngLoop =  0 to UBound(strDevelopers)
    strSQL = "Insert into tblDevelopers (Project,Developer) Values (" & _
                   rs!Project  & +,'" & strDevelopers(lngLoop) & "')"
......'write new record using the SQL string
Next
rs.movenext
Wend

-- 
Stuart