Splitting a field into many rows

[paveypop]

Hi all,

I have a problem this is part of my database

ID NAME DATARANGE ck1 ck2
1 dave 23-Jan-06,24-Jan-06,30-May-06 dataprojector s/table
2 John 23-Feb-06,24-Mar-06,30-Apr-06 s/table

What i would like to do is to run an maketable / updatetable query that splits out the daterange dates into seperate rows and also if there is a value in ck1 or ck2 replace the current value with either a 1 or 0.

So the above table would look like this.

ID NAME DATARANGE ck1 ck2
1 dave 23-Jan-06 1 1
2 dave 24-Jan-06 1 1
.
.
.
?? John 23-Feb-06 0 1
John 24-Mar-06 0 1

ETC

is this possible any help would be greatful

Thanks

Dave

 

[Jon K]

It's possible by means of queries only if [DATARANGE] uniformly contains three dates separated by comma in each record in the data table. (If the number of dates varies amongst the records, you will have to use VBA code.)

The attachment contains an Append query, which is based on a Union query. When you run the Append query, the data will be appended from the data table to the NewTable in the required format.
.

 

[paveypop]

Thanks for looking into it for me, but the daterange field does have different varying dates, but they are all comma seperated as this is part of a booking system, would you be able to advise on any vba code to do this..
Thanks in advance.

Dave

 

[Bat17]

Excuse me buttin in but this looked fun

This code assumes that there will always be at least one date!
Depending on your version you may need to set a reference to Microsoft DAO, in any module - Tools>references...

Sub AmmendTable()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim j As Integer
Dim lngID As Long
Dim varArray

Set rs = CurrentDb.OpenRecordset("tbltempIn") ' data to change
Set rs2 = CurrentDb.OpenRecordset("tbltemp") ' new table
With rs
    .MoveFirst
    Do While Not .EOF
        varArray = Split(rs!DataRange, ",")
        For j = 0 To UBound(varArray)
            lngID = lngID + 1
            rs2.AddNew
            rs2!ID = lngID
            rs2!Name = rs!Name
            rs2!ck1 = IIf(Nz(rs!ck1, "") = "", 0, 1)
            rs2!ck2 = IIf(Nz(rs!ck2, "") = "", 0, 1)
            rs2!DataRange = CDate(varArray(j))
                Debug.Print lngID; " - "; CDate(varArray(j))
            rs2.update
        Next j
        .MoveNext
    Loop
End With
Set rs = Nothing
Set rs2 = Nothing
End Sub

Peter