如何将表1中的几条记录的日期追加到表2的"日期列表"字段中

zonyoung　发表于：2009-11-24 23:59:32 　

复制

请问如何将附件表1中的几条记录的日期追加到表2对应的"日期列表"字段中,如下图所示:

将表1对应项目号和工号的日期追加到表2对应的日期列表里面(示例为147号的001项目)

点击下载此附件

Top

竹笛发表于：2009-11-25 00:39:08

要用一个ADO从表1去循环读取,同时用另一个ADO代码写入。也就是说，要声明两个rst记录集。

Top

点燃一支烟发表于：2009-11-25 21:11:52

http://www.accessoft.com/bbs/showtopic.asp?ID=6208 参阅

Top

马中洋发表于：2009-12-02 23:33:00

我用版主推荐的示例,但无法实现我的想法,请指点.

Dim sm, ch As String
    Dim hh As Integer
    Dim strSQL As String
    Dim rst1, rst2 As DAO.Recordset


    strSQL = "SELECT 2.SENSORID, 2.Badgenumber, 2.日期列表 FROM 2 ORDER BY 2.Badgenumber,2.SENSORID "
    Set rst1 = CurrentDb.OpenRecordset(strSQL)
    If rst1.EOF Or rst1.BOF Then Exit Sub
    Do Until rst1.EOF

    strSQL = ""
    strSQL = "SELECT 1.Badgenumber, 1.SENSORID, 1.日期 FROM 1 " & _
             "LEFT JOIN 2 ON (1.Badgenumber = 2.Badgenumber) AND (1.SENSORID = 2.SENSORID) " & _
             "ORDER BY 1.Badgenumber, 1.SENSORID"
        Set rst2 = CurrentDb.OpenRecordset(strSQL)
        sm = "": ch = ""
        Do Until rst2.EOF
        ch = Day(rst2("日期"))
        sm = sm & ch & ","

    rst2.MoveNext
    Loop
        CurrentDb.Execute "UPDATE 2 SET 2.日期列表 = '" & sm & "'"
    rst1.MoveNext
    Loop
    rst1.Close
    Set rst1 = Nothing
    rst2.Close
    Set rst2 = Nothing

点击下载此附件

Top

符鸿敏发表于：2009-12-05 22:13:54

看看我这示例行不？

点击下载此附件

Top

马中洋发表于：2009-12-06 20:09:15

感谢符鸿敏版主的指点.你的方法非常的简单好用.

我后来又想到了另外一种方法即用点燃一支烟版主推荐的思路实现了,在此十分感谢两位版主的及张老师的帮助.

我也将我的方法贴出来和大家共亨:

代码如下:

Dim sm, ch, sm1, ch1, mindate, maxdate, datefw As String
    Dim hh As Integer
    Dim strSQL As String
    Dim rst1, rst2 As DAO.Recordset



    strSQL = "SELECT 2.SENSORID, 2.Badgenumber, 2.日期列表 FROM 2 ORDER BY 2.Badgenumber,2.SENSORID "
    Set rst1 = CurrentDb.OpenRecordset(strSQL)
    If rst1.EOF Or rst1.BOF Then Exit Sub
    Do Until rst1.EOF

    strSQL = ""
    strSQL = "SELECT 1.Badgenumber, 1.SENSORID, 1.日期 FROM 1 " & _
    "WHERE ((1.Badgenumber = '" & rst1("badgenumber") & "') AND (1.SENSORID = '" & rst1("SENSORID") & "')) " & _
             "ORDER BY 1.Badgenumber, 1.SENSORID"
        Set rst2 = CurrentDb.OpenRecordset(strSQL)
        sm = "": ch = ""
        Do Until rst2.EOF
        ch = Day(rst2("日期"))
        sm = sm & ch & ","

    rst2.MoveNext
    Loop

        CurrentDb.Execute "UPDATE 1 LEFT JOIN 2 ON ([1].Badgenumber = [2].Badgenumber) AND ([1].SENSORID = [2].SENSORID) SET [2].日期列表 = '" & sm & "'" & _
                          "WHERE ((1.Badgenumber = '" & rst1("badgenumber") & "') AND (1.SENSORID = '" & rst1("SENSORID") & "')) "
    rst1.MoveNext
    Loop
    rst1.Close
    Set rst1 = Nothing
    rst2.Close
    Set rst2 = Nothing

附件可用三楼的附件.

Top

总记录：5篇页次：1/1 9 1 :

Access交流中心

[5分]如何将表1中的几条记录的日期追加到表2的"日期列表"字段中