ms-access - MS Access 将记录移动到字段中

Question

我与我不拥有且无法更改的数据库建立了 ODBC 连接。我要做的是使相关记录合并为一条记录。关系是一对多。

我有一个学生管理系统，想要导出一个提供自动标注服务(由调用收费)的调用列表。如果有多个学生住在那里，我希望能够只给一个房子打电话一次。

所需的调出文件结构:

PHONE     Inst1                  Inst2                  Inst3
555-5555  John was absent today  Jane was absent today  Joe was absent today

与现有数据相比:

PHONE     Inst
555-5555  John was absent today  
555-5555  Jane was absent today  
555-5555  Joe was absent today

有什么建议么？

Answer 1

我的第一个倾向是使用交叉表查询。但是，这可能会有点毛茸茸。

如何在模块中剪切一些 VBA 代码以将它们连接在一起，然后插入另一个表(如下所示 - 完全未经测试并且可能在某处损坏)？

dim strAbsent as string
dim currPhone as string
dim lastPhone as string
Dim db As Database
Dim rstAbsent As Recordset
Dim rstAbsentReport As Recordset

Set db = CurrentDb
Set rstAbsent = dbV.OpenRecordset("select * from Absent", _
                                    dbOpenDynaset, dbSeeChanges)
Set rstAbsentReport = dbV.OpenRecordset("select * from AbsentReport", _
                                        dbOpenDynaset, dbSeeChanges)

'...
do while not rstAbsentReport.EOF
    currPhone = rstAbsentReport("phone")
    lastPhone = currPhone 
    do while currPhone = lastPhone _
            and not rstAbsentReport.EOF
        strAbsent = strAbsent & ";" & rstAbsentReport ("comment")
        'Yes I know concatenating strings this way is terribly inefficient

        rstAbsentReport.MoveNext
        if not rstAbsentReport.EOF then
            currPhone = rstAbsentReport("phone")
        end if
    last
    rstAbsent.AddNew
    rstAbsent ("call") = strAbsent
    rstAbsent.Update
loop
'... clean up of recordset variables left as an exercise