通过条件对access多张表内的数据进行查询并汇总

yty773436272

在制品查询.zip (86.94 KB, 下载次数: 69)

2014-10-22 22:38 上传

点击文件名下载附件

，麻烦高手们帮帮我，我想通过excel界面对access数据库内的多张表进行条件查询，并对相同的车型和零件号进行汇总，具体要求和示例在excel附件中，感谢朋友们，帮帮忙。

yty773436272

麻烦高手帮帮忙，解决一下这个复杂问题，谢谢

魂断蓝桥

yty773436272 发表于 2014-10-23 07:25
麻烦高手帮帮忙，解决一下这个复杂问题，谢谢

Option Explicit
Sub A()
Dim cnn, rs As Object, SQL As String, TMP, TMP1 As String, I As Byte
Set cnn = CreateObject("ADODB.CONNECTION")
Set rs = CreateObject("ADODB.Recordset")
TMP = "SELECT A.车型,A.零件号,A.物资名称,A.数量1,B.数量1,C.数量1,D.数量1,E.数量1,F.数量1 FROM "
SQL = TMP & "(((((SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [供应入InBase] where 日期 BETWEEN  #" & [A2] & "#  AND #" & [b2] & "# "
If [C2] = "" And [D2] = "" Then
    SQL = SQL & " GROUP BY 车型,零件号,物资名称"
Else
    For I = 3 To 4
        If Cells(2, I) <> "" Then
            TMP1 = TMP1 & Cells(1, I) & "='" & Cells(2, I) & "' AND "
        End If
    Next
    TMP1 = Left(TMP1, Len(TMP1) - 4)
    SQL = SQL & " AND " & TMP1 & " GROUP BY 车型,零件号,物资名称"
End If
SQL = SQL & ") A LEFT JOIN (SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [供应出OutBase]  GROUP BY 车型,零件号,物资名称) B  ON " _
            & "A.车型=B.车型 AND A.零件号=B.零件号 AND A.物资名称=B.物资名称) left join " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [生产入InBase] GROUP BY 车型,零件号,物资名称) C ON " _
            & "A.车型=C.车型 AND A.零件号=C.零件号 AND A.物资名称=C.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [生产出OutBase] GROUP BY 车型,零件号,物资名称) D ON " _
            & "A.车型=D.车型 AND A.零件号=D.零件号 AND A.物资名称=D.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [外购入InBase] GROUP BY 车型,零件号,物资名称) E ON " _
            & "A.车型=E.车型 AND A.零件号=E.零件号 AND A.物资名称=E.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [外购出OutBase] GROUP BY 车型,零件号,物资名称) F ON " _
            & "A.车型=F.车型 AND A.零件号=F.零件号 AND A.物资名称=F.物资名称"
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0; Data Source=" & ThisWorkbook.Path & "\MyData.accdb;"
rs.Open SQL, cnn, 1, 1
If rs.RecordCount = 0 Then
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    MsgBox "无此记录": Exit Sub
End If
[A6:I999].ClearContents
[A6].CopyFromRecordset cnn.Execute(SQL)
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

yty773436272

魂断蓝桥 发表于 2014-10-23 13:40
Option Explicit
Sub A()
Dim cnn, rs As Object, SQL As String, TMP, TMP1 As String, I As Byte

感谢朋友，朋友帮我再继续完善一下，1、在我查询2014-8-2到2014-8-3时，供应的出库数只有100，但查询为400，在生产部和外购部这两天根本就没有数据，但也显示有数据，看了你的代码，发现你是以供应InBase数据表内的日期为基准进行统计计算的，我希望是该车型、零件号查询日期与数据库内每张表的日期进行匹配，在查询日期范围内才查询出来。2、对查询出来的数据添加边框，3、在执行新的查询条件时，清除上一次查询记录，比如我上一次查询8月份的数据，显示到对应的单元格中，但当我查询7月份数据时，由于数据库内没有7月份的数据，这时你提示没有此记录，但没有清除上一次查询出来的数据

魂断蓝桥

yty773436272 发表于 2014-10-23 18:36
感谢朋友，朋友帮我再继续完善一下，1、在我查询2014-8-2到2014-8-3时，供应的出库数只有100，但查询为 ...

Option Explicit
Sub A()
Dim cnn, rs As Object, SQL As String, TMP, TMP1, tmp2 As String, I As Byte, j As Integer
Set cnn = CreateObject("ADODB.CONNECTION")
Set rs = CreateObject("ADODB.Recordset")
Application.ScreenUpdating = False
Range("a6:i9999").ClearContents
Range("a6:i9999").Borders.LineStyle = 0
TMP = "SELECT A.车型,A.零件号,A.物资名称,A.数量1,B.数量1,C.数量1,D.数量1,E.数量1,F.数量1 FROM "
tmp2 = "where 日期 BETWEEN  #" & [A2] & "#  AND #" & [b2] & "# "
SQL = TMP & "(((((SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [供应入InBase] " & tmp2
If [C2] = "" And [D2] = "" Then
    SQL = SQL & " GROUP BY 车型,零件号,物资名称"
Else
    For I = 3 To 4
        If Cells(2, I) <> "" Then
            TMP1 = TMP1 & Cells(1, I) & "='" & Cells(2, I) & "' AND "
        End If
    Next
    TMP1 = Left(TMP1, Len(TMP1) - 4)
    SQL = SQL & " AND " & TMP1 & " GROUP BY 车型,零件号,物资名称"
End If
SQL = SQL & ") A LEFT JOIN (SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [供应出OutBase]  " & tmp2 & "GROUP BY 车型,零件号,物资名称) B  ON " _
            & "A.车型=B.车型 AND A.零件号=B.零件号 AND A.物资名称=B.物资名称) left join " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [生产入InBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) C ON " _
            & "A.车型=C.车型 AND A.零件号=C.零件号 AND A.物资名称=C.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [生产出OutBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) D ON " _
            & "A.车型=D.车型 AND A.零件号=D.零件号 AND A.物资名称=D.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [外购入InBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) E ON " _
            & "A.车型=E.车型 AND A.零件号=E.零件号 AND A.物资名称=E.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [外购出OutBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) F ON " _
            & "A.车型=F.车型 AND A.零件号=F.零件号 AND A.物资名称=F.物资名称"
[k1] = SQL
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0; Data Source=" & ThisWorkbook.Path & "\MyData.accdb;"
rs.Open SQL, cnn, 1, 1
If rs.RecordCount = 0 Then
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    MsgBox "无此记录": Exit Sub
End If
[a6].CopyFromRecordset cnn.Execute(SQL)
j = [a65536].End(xlUp).Row
Range("a6:i" & j).Borders.LineStyle = 1
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Application.ScreenUpdating = True
End Sub

yty773436272

魂断蓝桥 发表于 2014-10-24 08:44
Option Explicit
Sub A()
Dim cnn, rs As Object, SQL As String, TMP, TMP1, tmp2 As String, I As By ...

感谢蓝桥老师，不能以数据库“供应入InBase”作为查询的基础，现在“供应入InBase”数据表内有的数据你才能在所有的数据表内搜索，如果“供应入InBase”数据表内没有的车型和零件，在其它数据表内有的不能查询出来，麻烦朋友帮我再看看，我还想问你个问题，就是在“零件号”这个查询条件，我希望能实现模糊匹配，假如我想查询某个日期内包含611或61等其它的零件号，这个命令怎么添加呢，零件号太长，能不能输入几个代号，就能查出哪些零件号包含这些零件号在内的数据。

yty773436272

魂断蓝桥 发表于 2014-10-24 08:44
Option Explicit
Sub A()
Dim cnn, rs As Object, SQL As String, TMP, TMP1, tmp2 As String, I As By ...

蓝桥老师，麻烦你再帮我修改下，辛苦你啦，谢谢

魂断蓝桥

Option Explicit

Sub A()
Dim cnn, rs As Object, SQL As String, TMP, TMP1, tmp2 As String, I As Byte, j As Integer, arr
arr = Array("[供应入InBase]", "[供应出OutBase]", "[生产入InBase]", "[生产出OutBase]", "[外购入InBase]", "[外购出OutBase]")
Set cnn = CreateObject("ADODB.CONNECTION")
Set rs = CreateObject("ADODB.Recordset")
Application.ScreenUpdating = False
If [A2] = "" Or [B2] = "" Then
    MsgBox "开始日期；结束日期必须填写!": Exit Sub
End If
If [A2] > [B2] Then MsgBox "开始日期不能大于结束日期": Exit Sub
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0; Data Source=" & ThisWorkbook.Path & "\MyData.accdb;"
SQL = "delete * FROM [tmp]"
cnn.Execute (SQL)
SQL = ""
For I = 0 To UBound(arr)
    SQL = SQL & "select 车型,零件号,物资名称 FROM " & arr(I) & " where 日期 BETWEEN  #" & [A2] & "#  AND #" & [B2] & "# UNION "
Next
SQL = "SELECT * FROM (" & Left(SQL, Len(SQL) - 6) & ")"
rs.Open SQL, cnn, 1, 1
If rs.RecordCount = 0 Then
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    MsgBox "无此记录": Exit Sub
Else
    rs.Close
    SQL = "INSERT INTO [tmp] " & SQL
    cnn.Execute (SQL)
End If
SQL = ""
Range("a6:i9999").ClearContents
Range("a6:i9999").Borders.LineStyle = 0
TMP = "SELECT A.车型,A.零件号,A.物资名称,B.数量1,C.数量1,D.数量1,E.数量1,F.数量1,G.数量1 FROM ((((([tmp] A LEFT JOIN ("
tmp2 = " where 日期 BETWEEN  #" & [A2] & "#  AND #" & [B2] & "# "
SQL = TMP & "SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [供应入InBase]" & tmp2 & "GROUP BY 车型,零件号,物资名称) B  ON " _
            & "A.车型=B.车型 AND A.零件号=B.零件号 AND A.物资名称=B.物资名称) left join " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [供应出OutBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) C ON " _
            & "A.车型=C.车型 AND A.零件号=C.零件号 AND A.物资名称=C.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [生产入InBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) D ON " _
            & "A.车型=D.车型 AND A.零件号=D.零件号 AND A.物资名称=D.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [生产出OutBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) E ON " _
            & "A.车型=E.车型 AND A.零件号=E.零件号 AND A.物资名称=E.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [外购入InBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) F ON " _
            & "A.车型=F.车型 AND A.零件号=F.零件号 AND A.物资名称=F.物资名称) LEFT JOIN " _
            & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [外购出OutBase] " & tmp2 & "GROUP BY 车型,零件号,物资名称) G ON " _
            & "A.车型=G.车型 AND A.零件号=G.零件号 AND A.物资名称=G.物资名称"
If [C2] <> "" Or [D2] <> "" Then
    For I = 3 To 4
        If Cells(2, I) <> "" Then
            TMP1 = TMP1 & Cells(1, I) & " LIKE '%" & Cells(2, I) & "%' AND "
        End If
    Next
    TMP1 = " WHERE " & Left(TMP1, Len(TMP1) - 4)
    SQL = "SELECT * FROM (" & SQL & ")" & TMP1
End If
rs.Open SQL, cnn, 1, 1
If rs.RecordCount = 0 Then
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    MsgBox "无此记录": Exit Sub
End If
[a6].CopyFromRecordset cnn.Execute(SQL)
j = [a65536].End(xlUp).Row
Range("a6:i" & j).Borders.LineStyle = 1
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Application.ScreenUpdating = True
End Sub

在制品查询.rar (76.08 KB, 下载次数: 206)

2014-10-27 13:54 上传

点击文件名下载附件

yty773436272

魂断蓝桥 发表于 2014-10-27 13:54
Option Explicit

Sub A()

蓝桥老师，谢谢你无私的帮助，完美的解决啦，非常感谢你。还想请教一个问题，就是我想增加数据库内数据表，是不是就是改动：arr = Array("[供应入InBase]", "[供应出OutBase]", "[生产入InBase]", "[生产出OutBase]", "[外购入InBase]", "[外购出OutBase]","[其它数据表]" )
和增加： & "(SELECT 车型,零件号,物资名称,SUM(数量) AS 数量1 FROM [其它数据库] " & tmp2 & "GROUP BY 车型,零件号,物资名称) H ON " _
            & "A.车型=H.车型 AND A.零件号=H.零件号 AND A.物资名称=H.物资名称) LEFT JOIN " _,QI
其它还需要变动吗？

魂断蓝桥

yty773436272 发表于 2014-10-27 18:37
蓝桥老师，谢谢你无私的帮助，完美的解决啦，非常感谢你。还想请教一个问题，就是我想增加数据库内数据 ...

对的，两个地方都需要修改。