Access交流中心

北京 | 上海 | 天津 | 重庆 | 广州 | 深圳 | 珠海 | 汕头 | 佛山 | 中山 | 东莞 | 南京 | 苏州 | 无锡 | 常州 | 南通 | 扬州 | 徐州 | 杭州 | 温州 | 宁波 | 台州 | 福州 | 厦门 | 泉州 | 龙岩 | 合肥 | 芜湖 | 成都 | 遂宁 | 长沙 | 株洲 | 湘潭 | 武汉 | 南昌 | 济南 | 青岛 | 烟台 | 潍坊 | 淄博 | 济宁 | 太原 | 郑州 | 石家庄 | 保定 | 唐山 | 西安 | 大连 | 沈阳 | 长春 | 昆明 | 兰州 | 哈尔滨 | 佳木斯 | 南宁 | 桂林 | 海口 | 贵阳 | 西宁 | 乌鲁木齐 | 包头 |

怎样生成联合列的表

不务实  发表于:2009-06-26 15:16:49  
复制

表之间的联合,联合行可以,但联合列如何联???

 

 

点击下载此附件

 

Top
点燃一支烟 发表于:2009-06-26 17:10:14
你的本期是什么意思?从样表似乎看不出数据关系(又不是本期+期初=期末),不大明白

不务实 发表于:2009-06-26 17:40:53

是的,但为简化去掉了中间的那部分,  每个表的结构还不一样

 

原本是:

期初有两个           借方余额             贷方余额

本期也有两个         本期借方发生额       贷方发生额

期末也有两个         借方余额             贷方余额

 

并且有很多的表,我要从中取数,连起来生成新表

 

 

 

你不要管他的=关系,

只要连起来就行

 

 

 



曹光耀 发表于:2009-06-26 19:21:11
不务实 发表于:2009-06-26 21:22:36


不务实 发表于:2009-06-26 21:25:04


不务实 发表于:2009-06-26 21:25:37
Private Sub cmdSum_Click()
    Dim rs As New ADODB.Recordset
    Dim str As String
    Dim workhours As Integer
    Dim overday As Integer
    Dim rsID As New ADODB.Recordset
    Dim sTime As Date
    Dim eTime As Date
    Dim lTimes As Integer
    Dim earlyTimes As Integer
    Dim lateTimes As Integer
    Dim rsWorkTime As New ADODB.Recordset
    Dim absentTimes As Integer
    Dim flag As Integer
   
    sTime = Me.Year_Month & "-1"
    eTime = Me.Year_Month & "-31"
   
    If IsNull(Me.Year_Month) Then
    MsgBox ("请输入年月!")
    ElseIf IsNull(Me.StartTime) Then
    MsgBox ("请输入开始时间!")
    ElseIf IsNull(Me.EndTime) Then
    MsgBox ("请输入结束时间!")
    Else
    str = "select ID from Attendance_State where Year_Month = '" & Me.Year_Month & "'"
    Set rs = GetRS(str)
    flag = rs(0)
    rs.Close
    If flag > 0 Then
        MsgBox ("已经有过统计记录!")
        Exit Sub
    Else
    str = "select ID from Person"
    Set rsID = GetRS(str)
   
    While Not rsID.EOF
    workhours = 0
    '加班时间---------
    str = "select OverTime.Work_Hours from OverTime where OverTime.Work_Date between #"
    str = str & sTime & "# and #" & eTime & "# and OverTime.Person='"
    str = str & rsID(0) & "'"
    Set rs = GetRS(str)
    While Not rs.EOF
        workhours = workhours + rs(0)
        rs.MoveNext
    Wend
    rs.Close
    '-----------------
    '出差时间---------
    str = "select Errand.Start_Time, Errand.End_Time from Errand where Start_Time between #"
    str = str & sTime & "# and #" & eTime & "# and Person='"
    str = str & rsID(0) & "'"
    Set rs = GetRS(str)
    'Debug.Print str
    overday = 0
    While Not rs.EOF
        overday = overday + DateDiff("y", rs(0), rs(1))
        rs.MoveNext
    Wend
    rs.Close
    '-----------------
    '请假时间---------
    str = "select ID from Leave where Start_Time between #"
    str = str & sTime & " " & Me.StartTime & "# and #"
    str = str & eTime & " " & Me.EndTime & "# and Person='" & rsID(0) & "'"
    Set rs = GetRS(str)
    lTimes = 0
    While Not rs.EOF
        lTimes = lTimes + 1
        rs.MoveNext
    Wend
    rs.Close
    '-----------------
   

不务实 发表于:2009-06-26 21:26:02
'迟到早退---------
    str = "select In_Out,Io_Time from Attendance where Io_Date between #"
    str = str & sTime & "# and #" & eTime & "# and Person='" & rsID(0) & "'"
    Set rs = GetRS(str)
    str = "select * from WorkTime"
    Set rsWorkTime = GetRS(str)
    earlyTimes = 0
    lateTimes = 0
    While Not rs.EOF
    If DateDiff("h", rs(1), #12:00:00 PM#) >= 0 Then
        If rs(0) = 2 And DateDiff("s", rs(1), rsWorkTime("StartTimeAM")) < 0 Then
            lateTimes = lateTimes + 1
        End If
        If rs(0) = 1 And DateDiff("s", rs(1), rsWorkTime("EndTimeAM")) > 0 Then
            earlyTimes = earlyTimes + 1
        End If
    Else
        If rs(0) = 2 And DateDiff("s", rs(1), rsWorkTime("StartTimePM")) < 0 Then
            lateTimes = lateTimes + 1
        End If
        If rs(0) = 1 And DateDiff("s", rs(1), rsWorkTime("EndTimePM")) > 0 Then
            earlyTimes = earlyTimes + 1
        End If
     
    End If
  
    rs.MoveNext
    Wend
    rs.Close
    '旷工记录---------
    str = "select * from Attendance where Io_Time > #" & rsWorkTime("EndTimePM") & "# and "
    str = str & "Person='" & rsID(0) & "'"
    Set rs = GetRS(str)
    absentTimes = 0
    While Not rs.EOF
        absentTimes = absentTimes + 1
        rs.MoveNext
    Wend
    rs.Close
    '-----------------
    '-----------------
    '添加记录---------
    str = "insert into Attendance_State (Year_Month,Person,Work_Hour,Over_Hour,Leave_Hday,"
    str = str & "Errand_Hday,Late_Times,Early_Times,Absent_Times) values( '"
    str = str & Me.Year_Month & "','" & rsID(0) & "', 8," & workhours & ","
    str = str & lTimes * 2 & "," & overday * 2 & "," & lateTimes & "," & earlyTimes & ","
    str = str & absentTimes & ")"
    ExecuteSQL (str)
    '-----------------
    rsID.MoveNext
    Wend
    End If
    DoCmd.OpenForm ("FrmSumResult")
    Me.Visible = False
    End If
End Sub


不务实 发表于:2009-06-27 09:26:24

我要象这个例子中的方式.,例子中英文太多,而且名称好多相同,多看不懂

点击下载此附件

不务实 发表于:2009-06-27 09:27:14
是那个例子中   统计结果   窗体代码

843151504 发表于:2009-07-09 17:11:29
只要认真的看添加记录这儿就行了

总记录:10篇  页次:1/1 9 1 :