根据列名更新 MS Access 中的表 [英] Updating a table in MS Access based upon Column Names

问题描述

我继承了一个远远超出我的 MS Access 项目:表 A 有考试数据，所以 A 中的每个字段都是考试项目的名称，值是在该项目上获得的分数.我的任务是将这些结果转换为表 B，其中有一个字段用于项目名称，一个字段用于对应的值.

I have inherited a MS Access project that is way beyond me: Table A has exam data, so each field in A is the name of an exam item and the value is the points earned on that item. My task is to convert those results to Table B, which has one field for the item name and one field for the corresponding value.

所以它看起来像这样:

Table A:
  A.Item01
  A.Item02
  etc.

Table B:
   NameofFieldinTableA
   Result

...所以如果 A.Item01 = 1，我希望它更新到表 B:

...so if A.Item01 = 1, I'd like that to update to Table B as:

NameOfFieldinTableA = Item01结果 = 1

NameOfFieldinTableA = Item01 Result = 1

我很抱歉无法更好地解释这一点，但这让我完全被难住了.我唯一的猜测是用 VBA 写一些东西?

My apologies for not being able to better explain this, but this has got me completely stumped. My only guess is to write something in VBA?

感谢任何帮助！

推荐答案

是的，您将使用 VBA 来遍历表中的字段 - 使用 NAME 属性获取 NameofFieldinTableA，使用 VALUE 属性获取结果.所以 VBA 看起来像这样:

Yes, you'd use VBA to loop through the fields in your table - using the NAME property to get the NameofFieldinTableA and the VALUE property to get the Result. So the VBA would look something like this:

Dim rsA as dao.recordset
Dim rsB as dao.recordset
dim fld as dao.field

Set rsA = CurrentDB.openRecordset("TableA", dbopendynaset)
Set rsB = CurrentDB.openRecordset("TableB", dbopendynaset)

Do Until rsA.EOF
   For each fld in rsA.Fields
       rsB.AddNew
       rsB!NameofFieldinTableA = fld.Name
       rsB!Result = fld.Value
       rsB.Update
   Next
   rsA.MoveNext
Loop

rsA.close
rsB.Close

Set rsA = Nothing
Set rsB = Nothing

这篇关于根据列名更新 MS Access 中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！