Loops through Access Table and for each Column wit

问题:

I think this should be simple, but I can't find the right way to do it. I have a table with an ID number column, and 10 rows following it labeled Question #1, Question #2, and so forth.

There are no duplicate ID numbers, but each ID number could have more than one row of questions.

I would like to take the ID row and for each different question where applicable create a new row with the same ID. So if an ID number has a question listed under the Question #1 and Question #2, Id like to create a duplicate for that ID number and have have both questions listed under one column Lets call it "Total Questions", and grouped by that ID number. This can be done by creating a new table.

Example:

From:

+-------+---------------------------+---------------------------+
|  ID   |        Question #1        |        Question #2        |
+-------+---------------------------+---------------------------+
| 11111 | Was it notated correctly? | Was it completed on time? |
+-------+---------------------------+---------------------------+

To:

+-------+-------------------------------------+
|  ID   | Total Questions                     |
+-------+-------------------------------------+
| 11111 | Was it notated correctly?           |
| 11111 | Was it completed on time?           |
+-------+-------------------------------------+

回答1:

A simple solution using DAO

sub SomeProcedure()
    Dim db as DAO.Database, recIn as DAO.Recordset, recOut as DAO.Recordset

    Set db = currentdb()
    Set recIn = db.openRecordset("yourQuestionsInputTable", dbOpenDynaset, dbReadOnly)
    Set recOut = db.openRecordset("yourQuestionsOutputTable", dbOpenDynaset, dbEditAdd)

    with recIn
        .moveFirst
        do
            for i = 1 to .Fields.count
                if left(.Fields(i).Name, 8) = "Question" then
                    recOut.addNew
                        recOut.Fields("Id") = .fields("Id")
                        recOut.Fields("Total Questions") = .Fields(i)
                    recOut.update
                end if
            next i
            .moveNext
        loop until .EOF
    end with
    recIn.close
    recOut.close
    db.close
end sub

---

The explanation:

What I'm doing is:

1. Read each record from the input table
2. For each column wich name begins with "Question", create a new record in the output table, with the Id of the input table, and the value of the selected column.

---

This is just a draft. You'll need to tweak the code to fit your needs.

Hope this helps.

---

Alternatives

After thinking a little, I may have an alternative to the problem you mention in your comments.

I think you can change the loop like this:

' You'll need a variable of type Field
Dim f as DAO.Field ' Check if this is right
' Some code
with recIn
    .moveFirst
    do
        for f in .Fields
            if left(f.Name, 8) = "Question" then
                recOut.addNew
                    recOut.Fields("Id") = .Fields("Id").Value
                    recOut.Fields("Total Questions") = .Fields(f.Name).Value
                recOut.update
            end if
        next f
        .moveNext
    loop until .EOF
end with
' More code

Instead of iterating on the Fields collection with an index, this will iterate with any Field member in it. That should avoid the "Item not found in collection" issue.

Warning: Not tested

回答2:

Try a couple queries like this:

SELECT ID, Question1 AS TotalQuestions
INTO NewTable
FROM OriginalTable;

SELECT ID, Question2 AS TotalQuestions
INTO NewTable
FROM OriginalTable;