遍历访问表并为每个具有数据的列循环 [英] Loops through Access Table and for each Column with Data

问题描述

我认为这应该很简单，但是我找不到正确的方法.我有一个带有ID号列的表，其后有10行，分别标记为问题#1，问题#2，依此类推.

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.

没有重复的ID号，但是每个ID号都可以包含多于一行的问题.

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

我想参加ID行，并在适用的情况下针对每个不同的问题创建一个具有相同ID的新行.因此，如果一个ID号在问题#1和问题#2下列出了一个问题，那么我想为该ID号创建一个重复项，并且将两个问题都列在一个列下，将其称为总问题"，并按该问题进行分组身份证号.这可以通过创建一个新表来完成.

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.

示例:

发件人:

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

收件人:

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

推荐答案

使用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

---

说明:

我正在做的是:

1. 从输入表中读取每条记录
2. 对于每个名称以"Question"开头的列，请在输出表中创建一个新记录，并带有输入表的Id和所选列的值.

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.

希望这会有所帮助.

替代

经过一番思考，我可能会替代您在评论中提到的问题.

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

代替对带有索引的Fields集合进行迭代，这将对其中的任何Field成员进行迭代.那应该避免出现在集合中找不到项目"的问题.

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.

警告:未测试

这篇关于遍历访问表并为每个具有数据的列循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！