MSAccess 1 record to multiple records...(VBA thing maybe?) 2

[ETID]

Hi Everyone, I am starting in this forum because i'm thinking this might have to be a custom function.

I have some records in this format...

Ref Class Date_in
A1234 3,2,3A 01/01/04

and need it to be this...

Ref Class Date_in
A1234 3 01/01/04
A1234 2 01/01/04
A1234 3A 01/01/04

Do I have to do this with code or is there some sort of weird union query that I can run?

In either case I'm stumped on how to proceed.

 

[RoyVidar]

Erm - as far as I know/in my humble opinion - the "custom function" relating to this question, is most often called "normalization", and should have resulted in a table design close to your expected results - and should have been performed when the database was created.

That said, should be a simple operation, opening a recordset based on the first table, appening the new records to the new table. Just thrown together, does not take into account possible Null in the class field, would need some errorhandling... using ADO:

[tt]Sub etidtest()
Dim rs As ADODB.Recordset
Dim strSql As String
Dim arr() As String
Dim lngCounter As Long
Set rs = CurrentProject.Connection.Execute("etid1", , adCmdTable)
Do While Not rs.EOF
If InStr(rs.Fields("class"), ",") > 0 Then
arr = Split(rs.Fields("class").Value, ",")
For lngCounter = 0 To UBound(arr)
strSql = "insert into etid2 (ref, [class], date_in) " & _
"values ('" & rs.Fields("ref").Value & "','" & _
arr(lngCounter) & "',#" & _
Format$(rs.Fields("date_in").Value, "yyyy-mm-dd") & "#)"
CurrentProject.Connection.Execute (strSql)
Next lngCounter
Else
strSql = "insert into etid2 (ref, [class], date_in) " & _
"values ('" & rs.Fields("ref").Value & "','" & _
rs.Fields("class").Value & "',#" & _
Format$(rs.Fields("date_in").Value, "yyyy-mm-dd") & "#)"
CurrentProject.Connection.Execute (strSql)
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub[/tt]

Roy-Vidar

 

[ETID]

Thank you...ya done good