CODEWITHSUNDEEP
sqlms-accessvba
bluecat
bluecat

Reputation: 219

Iterating through columns in access/sql Solution?

Not sure if this can only be done in VBA but it would be a good skill to learn if I can do this entirely in SQL statements.

Say I have two tables, the first as below, with a column up until 12 (I didn't think it was designed very well)

Table 1

Project ID Month1 Dollars1 Month2 Dollars2 Month3 Dollars3

and a second Table 2

Project Id Month Dollars

Is there a way where I can iterate through the columns for each row in Table 1, and create an entry for each Month and Dollar field?

I can see how I can do this in excvel-VBA, loop through columns and then add new for each instance of month and dollar or something to that effect, but access-vba is a bit more cryptic to me, though SQL is quite intuitive to me and if it is even possible I'd love to know!

Best, Kurt

Upvotes: 0

Views: 686

Answers (2)

Horaciux
Horaciux

Reputation: 6477

Try this approach

INSERT INTO Table2
SELECT ProjectID, Month1, Dollars1 FROM Table1
UNION ALL
SELECT ProjectID, Month2, Dollars2 FROM Table1
UNION ALL
SELECT ProjectID, Month3, Dollars3 FROM Table1
UNION ALL
SELECT ProjectID, Month4, Dollars4 FROM Table1
UNION ALL
SELECT ProjectID, Month5, Dollars5 FROM Table1
UNION ALL
SELECT ProjectID, Month6, Dollars6 FROM Table1
UNION ALL
SELECT ProjectID, Month7, Dollars7 FROM Table1
UNION ALL
SELECT ProjectID, Month8, Dollars8 FROM Table1
UNION ALL
SELECT ProjectID, Month9, Dollars9 FROM Table1
UNION ALL
SELECT ProjectID, Month10, Dollars10 FROM Table1
UNION ALL
SELECT ProjectID, Month11, Dollars11 FROM Table1
UNION ALL
SELECT ProjectID, Month12, Dollars12 FROM Table1
UNION ALL

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112342

With DAO you can do it like this:

Dim db As DAO.Database, rs As DAO.Recordset
Dim month As Long
Dim dat, amount As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Table1")
Do Until rs.EOF
    For month = 0 To 11
        dat = rs(2 * month + 1)
        amount = rs(2 * month + 2)
        'TODO: Process dat and amount
    Next month
    rs.MoveNext
Loop
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing

Here I access the columns by number. Column #0 is [Project ID], column #1 Month1 etc.

With ADO the procedure is very similar:

Dim rs As ADODB.Recordset
Dim month As Long
Dim dat, amount As Variant

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tlkpMonth", CurrentProject.Connection
Do Until rs.EOF
    For month = 0 To 11
        dat = rs(2 * month + 1)
        amount = rs(2 * month + 2)
        'TODO: Process dat and amount
    Next month
    rs.MoveNext
Loop
rs.Close: Set rs = Nothing

See also: Choosing between DAO and ADO

Upvotes: 1

Related Questions