Excel VBA - 内部连接问题

Excel VBA - INNER JOIN Issue

提问by Reznor

I've got an issue with an Inner Join statement I am using to access data from my Access Database. What I am expecting to happen is that I run through the record set for each product. When a product was ordered more then once, I dont add it to the excel sheet, instead I increment the number ordered and the total cost.

我用来访问 Access 数据库中的数据的 Inner Join 语句有问题。我期望发生的是我遍历每个产品的记录集。当一个产品被订购不止一次时，我不会将它添加到 Excel 表中，而是增加订购的数量和总成本。

The problem I am having is that instead of it working the way I described above, it is adding a product to the excel sheet for every time it was ordered. I have discovered that it is printing the products in the order that they were ordered (By their OrderID) which is not included in my code.

我遇到的问题是，它不是按照我上面描述的方式工作，而是在每次订购时将产品添加到 Excel 表中。我发现它正在按照我的代码中未包含的订购顺序（按他们的 OrderID）打印产品。

Any help?

有什么帮助吗？

Here is the code :

这是代码：

Public Sub WorksheetLoop()


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stDB As String, stSQL As String, stSQLTwo As String, stProvider As String
Dim sheetName As String, stProdName As String
Dim suppNum As Integer, prodNum As Integer
Dim WS_Count As Integer
Dim I As Integer

suppNum = 1
prodNum = 1

stDB = "Data Source= " & ThisWorkbook.Path & "\obsDatabase.accdb"
stProvider = "Microsoft.ACE.OLEDB.12.0"

'Opening connection to database
With cn

    .ConnectionString = stDB
    .Provider = stProvider
    .Open

End With


' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 2 To WS_Count

    ActiveWorkbook.Worksheets(I).Range("A1") = "Company Name - " + ActiveWorkbook.Worksheets(I).Name + ""
    ActiveWorkbook.Worksheets(I).Range("A2") = "Item Number"
    ActiveWorkbook.Worksheets(I).Range("B2") = "Description"
    ActiveWorkbook.Worksheets(I).Range("C2") = "Unit"
    ActiveWorkbook.Worksheets(I).Range("D2") = "Cost Per Unit"
    ActiveWorkbook.Worksheets(I).Range("E2") = "Quantity"
    ActiveWorkbook.Worksheets(I).Range("F2") = "Total Cost"
    ActiveWorkbook.Worksheets(I).Range("G2") = "Amount Remaining"

    'Function to retrieve info!

    stSQL = "SELECT Products.ProductID, Products.ProductName, Products.ProductDescription, Products.ProductUnit, LineItems.UnitPrice, LineItems.Quantity, LineItems.TotalPrice " & _
    "FROM Products INNER JOIN LineItems ON LineItems.ProductID = Products.ProductID WHERE Products.SupplierID = " & suppNum & " "
    rs.Open stSQL, cn

    With rs

        Do Until .EOF


            If ActiveWorkbook.Worksheets(I).Range("A65536").End(xlUp) = rs.Fields("ProductName") Then

                If ActiveWorkbook.Worksheets(I).Range("D65536").End(xlUp) = rs.Fields("UnitPrice") Then

                    ActiveWorkbook.Worksheets(I).Range("E65536").End(xlUp) = ActiveWorkbook.Worksheets(I).Range("E65536").End(xlUp) + rs.Fields("Quantity")
                    ActiveWorkbook.Worksheets(I).Range("F65536").End(xlUp) = ActiveWorkbook.Worksheets(I).Range("F65536").End(xlUp) + rs.Fields("TotalPrice")

                End If

            Else

                ActiveWorkbook.Worksheets(I).Range("A65536").End(xlUp).Offset(1, 0) = rs.Fields("ProductName")
                ActiveWorkbook.Worksheets(I).Range("B65536").End(xlUp).Offset(1, 0) = rs.Fields("ProductDescription")
                ActiveWorkbook.Worksheets(I).Range("C65536").End(xlUp).Offset(1, 0) = rs.Fields("ProductUnit")
                ActiveWorkbook.Worksheets(I).Range("D65536").End(xlUp).Offset(1, 0) = rs.Fields("UnitPrice")
                ActiveWorkbook.Worksheets(I).Range("E65536").End(xlUp).Offset(1, 0) = rs.Fields("Quantity")
                ActiveWorkbook.Worksheets(I).Range("F65536").End(xlUp).Offset(1, 0) = rs.Fields("TotalPrice")

            End If
            rs.MoveNext

        Loop

    End With


    rs.Close
    suppNum = suppNum + 1

    ActiveWorkbook.Worksheets(I).Columns("A:A").EntireColumn.AutoFit
    ActiveWorkbook.Worksheets(I).Columns("B:B").EntireColumn.AutoFit
    ActiveWorkbook.Worksheets(I).Columns("C:C").EntireColumn.AutoFit
    ActiveWorkbook.Worksheets(I).Columns("D:D").EntireColumn.AutoFit
    ActiveWorkbook.Worksheets(I).Columns("E:E").EntireColumn.AutoFit
    ActiveWorkbook.Worksheets(I).Columns("F:F").EntireColumn.AutoFit
    ActiveWorkbook.Worksheets(I).Columns("G:G").EntireColumn.AutoFit

Next I

cn.Close


End Sub

回答by Kevin Morrissey

I'm not sure if I am missing the point. But could you clarify your end goal? Does it have to be done via Excel VBA?

我不确定我是否错过了重点。但是你能澄清你的最终目标吗？是否必须通过 Excel VBA 完成？

If what you are trying to achieve is a tab with each suppliers orders on, with one row per product and a total quatity for that product, then I would consider creating a query in the database itself and pass the supplier id parameter and any other parameters to the query. The query could then handle the grouping and counting of products and quatitys as this would be an aggregrate query.

如果您要实现的是每个供应商订单的选项卡，每个产品一行和该产品的总数量，那么我会考虑在数据库本身中创建一个查询并传递供应商 id 参数和任何其他参数到查询。该查询然后可以处理产品和数量的分组和计数，因为这将是一个聚合查询。

This way you can have a refreshable query on each tab and wrtie VBA to refresh them individually or all together whichever suits your needs.

通过这种方式，您可以在每个选项卡上都有一个可刷新的查询，并使用 VBA 来单独刷新它们，或者根据您的需要一起刷新它们。

I would always try to avoid complex VBA coding as it's buggy at the best of times and becomes difficult to maintain once distributed.

我总是尽量避免复杂的 VBA 编码，因为它在最好的时候会出错，并且一旦分发就变得难以维护。

Another option would be pull all the product data into another tab which you could Hide via VBA and use formula like SUMPRODUCT to display the information on the various tabs. Or use a combo style box to select your supplier and dynamically change the result set.

另一种选择是将所有产品数据拉到另一个选项卡中，您可以通过 VBA 隐藏该选项卡，并使用 SUMPRODUCT 等公式显示各个选项卡上的信息。或者使用组合样式框选择您的供应商并动态更改结果集。

As I said in the beginning I may be missing the point, but if not and you would like help with my option(s) let me know, and if I am please clarify your question.

正如我在开始时所说的那样，我可能没有抓住重点，但如果没有，并且您希望我的选项得到帮助，请告诉我，如果我是，请澄清您的问题。

For your INNER JOIN issue, you would need to use an agregate query not a stright forward select. This is because your database (I assume) can have one supplier which can order the same product more than once (A one to Many relationship), from what you have supplied you have a quantity column in the LineItems table so I assume the duplicate product ID is from two or more seperate orders from the same supplier. Here is an example of the query, also consider aliasing your tables names, it makes following the code easier.

对于您的 INNER JOIN 问题，您需要使用聚合查询而不是直接选择。这是因为您的数据库（我假设）可以有一个供应商可以多次订购同一产品（一对多关系），根据您提供的内容，您在 LineItems 表中有一个数量列，所以我假设重复产品ID 来自同一供应商的两个或多个单独的订单。这是查询的示例，还可以考虑为表名设置别名，这样可以更轻松地遵循代码。

SELECT 
 p.ProductID
,p.ProductName
,p.ProductDescription
,p.ProductUnit
,SUM(l.UnitPrice)
,SUM(l.Quantity)
,SUM(l.TotalPrice)
FROM Products p
INNER JOIN LineItems l
ON l.ProductID = p.ProductID 
WHERE p.SupplierID = 1
GROUP BY 
 p.ProductID
,p.ProductName
,p.ProductDescription
,p.ProductUnit

Regards Kevin

问候凯文

回答by barrowc

You're only ever comparing the current product name to the last line added to the worksheet but nothing in your SQL query enforces any ordering to the results.

您只是将当前产品名称与添加到工作表的最后一行进行比较，但 SQL 查询中没有任何内容强制对结果进行任何排序。

This would be OK if the data was returned like this:

如果数据是这样返回的，那就没问题了：

ProductName, Quantity

foo, 7
foo, 4
bar, 3

but would not be OK if the data was returned like this:

但如果数据是这样返回的，那就不行了：

ProductName, Quantity

foo, 7
bar, 3
foo, 4

You could use an ORDER BY clause and work with your current macro but, as other people have pointed out, you could use SQL to combine the data and this should be a simpler solution

您可以使用 ORDER BY 子句并使用当前的宏，但正如其他人指出的那样，您可以使用 SQL 来组合数据，这应该是一个更简单的解决方案