使用SQL“Group By”的组数据在Excel VBA中的功能 [英] Group Data using SQL "Group By" function in Excel VBA

问题描述

我有两个表（1和2）应该被合并到第三个（3）中。

I have two tables (1&2) that should be merged into a third (3) one.

表1：F_Number，A_Number，A_Weight

表2：A_Number，A_Country

表3：F_Number，A_Country，A_Weight

Table1: F_Number, A_Number, A_Weight
Table2: A_Number, A_Country
Table3: F_Number, A_Country, A_Weight

该thrid表应按F_Number分组A_Weight A_Country具有相同的价值。到目前为止，表的加入工作正常：

The thrid table should be grouped by F_Number summing up A_Weight where A_Country has the same value. The joining of the tables works fine so far:

Dim strSQL As String
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String
Dim sconnect As String

DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect
    strSQL = "Select [Table1$].[F_Number], [Table2$].[A_Country], [Table1$].[A_Weight] From [Table1$] Inner Join [Table2$] On [Table2$].[A_Number]=[Table1$].[A_Number]"
    mrs.Open strSQL, Conn
    ActiveSheet.Range("A2").CopyFromRecordset mrs
    mrs.Close
Conn.Close

结果是一个像这个：

F_Number;一个国家; A_Weight

F_1;我们; 10％

F_1：US; 10％

F_1;英国; 80％

F_2;英国; 100％

F_Number; A_country; A_Weight
F_1; US; 10%
F_1: US; 10%
F_1; UK; 80%
F_2; UK; 100%

我希望这个表按A_Weight分组，如下所示：

I want this table to be grouped by "A_Weight" like this:

F_Number;一个国家; A_Weight

F_1;我们; 20％

F_1;英国; 80％

F_2;英国; 100％

F_Number; A_country; A_Weight
F_1; US; 20%
F_1; UK; 80%
F_2; UK; 100%

我不太了解SQL，但是我迄今为止所尝试的是总结A_Weight并将F_Number的结果分组，这给我一个错误消息。我正在使用德国办公室版本，因此我只能尝试翻译错误：自动化错误。我仍然认为它应该像这样工作。

I do not know much about SQL, but what I tried so far is to sum up A_Weight and Group the result by F_Number, which gives me an error message. I am using a german office version, thus I can only try to translate the error: "Automatization Error". I am still thinking it should work something like this.

这是我的不工作的代码为SQL-String：

Here is my non-working code for the SQL-String:

strSQL = "Select [Table1$].[F_Number], [Table2$].[A_Country], Sum([Table1$].[A_Weight]) From [Table1$] Inner Join [Table2$] On [Table2$].[A_Number]=[Table1$].[A_Number] Group By [Table1$].[F_Number]"

推荐答案

你的代码很好您只需要修改SQL查询。

Your code is fine; you just need to modify the SQL query.

strSQL = "SELECT [Table1$].[F_Number], [Table2$].[A_Country], SUM([Table1$].[A_Weight]) " & _
         "FROM [Table1$], [Table2$] " & _
         "WHERE [Table2$].[A_Number] = [Table1$].[A_Number] " & _
         "GROUP BY [Table1$].[F_Number], [Table2$].[A_Country]"

这篇关于使用SQL“Group By”的组数据在Excel VBA中的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！