Excel VBA - INNER JOIN Issue

Question

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.

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.

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

Does the worksheet already contain data? If not, why are you not using TransferSpreadsheet and a grouping query? You can loop through the supplier list (+1 is not a good idea) and alter the sql of a query to expoer for each supplier. The concatenator in VBA is &, not +, so "Company Name - " & Activ ... The plus sign can lead to problems with nulls, so it is usually only used in special cases. — Fionnuala
– Fionnuala, Commented Oct 22, 2012 at 9:15

Kevin Morrissey · Accepted Answer · 2012-10-22 00:23:44Z

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?

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.

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.

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.

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.

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.

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

barrowc · Accepted Answer · 2012-10-28 02:59:16Z

0

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.

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

answered Oct 28, 2012 at 2:59

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

Collectives™ on Stack Overflow

Excel VBA - INNER JOIN Issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related