Loop through columns extracting field names

[zestee]

Hi, first time poster, be gentle with me!

I have a table (called Raw) which I'd like to dump into a new table (called Working). Four of the fields are static and will be repeated each time. However there are 104 fields of data (each named by the date the sales are for) which I'd like to extract, using the name of the field as the value for a new field.

I've attached an example of the raw data, and how I'd like it to look in the new working table.

Is there any way I can do this by counting columns rather than using their names? This would be ideal, as I have to clean this sort of data all the time, so it would be good to be able to use the same code over various formats of raw data.

TIA!

[twinnyfo]

zestee,

Welcome to Bytes!

Do you have 104 fields of Dates or 104 total fields (the four standard fields + 100 Dates)? Are there always 104?

These questions are important, because if the raw data is always the same, then it makes things a bit easier.

Below, I've assumed a total of 108 fields (four standard + 104 Dates). I think your spreadsheet listed a Date instead of the number of sales, that that was my assumption.

Double check tables and field names, and this should be a good place to start.....

Public Sub FieldNames()
    Dim db As Database
    Dim rst1 As Recordset
    Dim rst2 As Recordset
    Dim strSQL As String
    Dim intField As Integer

    Set db = CurrentDb()
    strSQL = "SELECT * FROM tblRaw;"
    Set rst1 = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rst1.RecordCount = 0 Then
        strSQL = "SELECT * FROM tblWorking;"
        Set rst2 = db.OpenRecordset(strSQL, dbOpenDynaset)
        rst1.MoveFirst
        Do While Not rst1.EOF
            For intField = 4 To 107
                With rst2
                    .AddNew
                    !prodname = rst1!prodname
                    !prodcode = rst1!prodcode
                    !mktname = rst1!mktname
                    !measure = rst1!measure
                    !Date = CDate(rst1.Fields(intField).Name)
                    !Sales = rst1.Fields(intField).Value
                    .Update
                End With
            Next intField
            rst1.MoveNext
        Loop
        rst2.Close
        Set rst2 = Nothing
    End If
    rst1.Close
    db.Close
    Set rst1 = Nothing
    Set db = Nothing
End Sub

Hope this helps!

[zestee]

twinnyfo, you're a rockstar! I did a little jig, am so stoked it works :)

[twinnyfo]

Glad I could be of assistance.