• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help on SQL Statement in VBA

A

abhay_547

New Member
I want to write below SQL Statement in excel macro which I pulled from MS Access query since I have the same access db's table data in my excel sheet, i want to run the query in excel to get the output instead of using MS Access.

Also I want to add an additional condition to the below SQL statement, that is to also exclude the rows which shouldn't be captured in output for e.g. if the total sum of Qty column for Trade Exec row items is 10 and Total Sum of Trade Alloc row items is 22 then Macro should take only those rows for which the sum total of Trade Alloc label row items (along with Trade Exec label row items) is 10 and copy the same to next sheet.


MS Access Query SQL Statement:
Code: 
SELECT [Sample Rec Data].[Name of Client], [Sample Rec Data].Price, Sum([Sample Rec Data].Qty) AS SumOfQty, [Sample Rec Data].Label
FROM [Sample Rec Data]
GROUP BY [Sample Rec Data].[Name of Client], [Sample Rec Data].Price, [Sample Rec Data].Label
HAVING ((([Sample Rec Data].Label)='TRADE ALLOC' Or ([Sample Rec Data].Label)='TRADE EXEC'));


MS Excel VBA SQL Statement which I have got so far:
Code: 
   sSQLSting = "SELECT [Sample Rec Data$].[Name of Client], [Sample Rec Data$].[Price],[Sample Rec Data].Label, SUM([Sample Rec Data].[Qty]) " & _
         "FROM [Sample Rec Data$]" & _
         "GROUP BY [Sample Rec Data$].[Name of Client], [Sample Rec Data$].[Price], [Sample Rec Data$].[Label]"
 
Try:

Code: 
  sSQLSting = "SELECT [Sample Rec Data$].[Name of Client], [Sample Rec Data$].[Price], [Sample Rec Data].Label, SUM([Sample Rec Data].[Qty]) " & _
         "FROM [Sample Rec Data$]" & _
         "GROUP BY [Sample Rec Data$].[Name of Client], [Sample Rec Data$].[Price], [Sample Rec Data$].[Label]"
 
Hi Hui, thanks for your quick response but I just realized that i don't want the grouping to be done since it's actually summing the Qty when the other 3 column values are same .i.e. Price, Label and Client Name instead I just want to select the rows where the Price, Label and Client Name are same and copy them as it is to the next sheet.

Also the below code is taking all the Label column row items, where else I want it to filter for only those row items where the Label is = TRADE Exec or Trade Alloc by using "having" and also compare the total of Qty column between Trade Exec and Trade Alloc line items so for e.g. if the total sum of Qty column for Trade Exec row items is 10 and Total Sum of Trade Alloc row items is 22 then Macro should take only those rows for which the sum total of Trade Alloc label row items is 10 and copy the same to next sheet along with Trade Exec row items for which the Qty column total is also 10.

Code: 
sSQLSting = "SELECT [Sample Rec Data$].[Name of Client], [Sample Rec Data$].[Price], [Sample Rec Data$].Label, SUM([Sample Rec Data$].[Qty]) " & _
        "FROM [Sample Rec Data$]" & _
        "GROUP BY [Sample Rec Data$].[Name of Client], [Sample Rec Data$].[Price], [Sample Rec Data$].[Label]"
 
Upload sample workbook, with manually created sample output you want.

In general, using SQL to extract duplicate records, you'd use 2 stage query.

1. Use Group By and Having Count(*) > 1
2. Using above query as left table, join original back to it using Left Join.

Ex: Basic syntax
Code: 
SELECT * FROM
(SELECT [COL1], COUNT(*) as [Count] FROM [Sheet1$]
GROUP BY [COL1]
HAVING COUNT(*) > 1) as a
LEFT JOIN [Sheet1$] as b
ON a.[COL1] = b.[COL1]

NOTE: You could specify table in outer SELECT to extract only columns from b.
 
Last edited:
PFA Sample file. First tab consists of raw data/source data and second tab consists of desired output/result.
 

Attachments

  • Sample Reconciliation Data macro.xlsm
    21.1 KB · Views: 4
As a means to convert manually created queries to VBA, attached is a converter that is based in Access.
 

Attachments

  • SQLConverter.zip
    11.1 KB · Views: 3
Hmm, it isn't clear to me what condition you are using to get the result.

As well, since ADO has known issue of creating memory leak when open workbooks are queried. I'd recommend separating out data workbook from workbook containing result.

For an example to query all records that has 3 duplicate fields (Name of Client, Price & Label)...

Code: 
Select b.*
From (
    SELECT [Name of Client], [Price], [Label], Count(*)
    From [Sample Rec Data$]
    Group by [Name of Client], [Price], [Label]
    Having Count(*) > 1) as a
Left Join [Sample Rec Data$] as b
ON a.[Name of Client] = b.[Name of Client] and
    a.[Price] = b.[Price] and a.[Label] = b.[Label]

This returns following.
Code: 
Altavista Pty Ltd    125    1    Trade Alloc
Altavista Pty Ltd    125    8    Trade Alloc
Altavista Pty Ltd    125    3    Trade Alloc
Altavista Pty Ltd    125    2    Trade Exec
Altavista Pty Ltd    125    5    Trade Exec
Altavista Pty Ltd    125    2    Trade Exec
Regime International    24.55    20    Trade Alloc
Regime International    24.55    6    Trade Alloc
Regime International    24.55    6    Trade Alloc
Regime International    24.55    1    Trade Exec
Regime International    24.55    1    Trade Exec
Regime International    24.55    10    Trade Exec
Xiaoli Pty Ltd    123    18    Give In Alloc
Xiaoli Pty Ltd    123    14    Give In Alloc
Xiaoli Pty Ltd    172.15    6    Trade Alloc
Xiaoli Pty Ltd    172.15    2    Trade Alloc
Xiaoli Pty Ltd    172.15    3    Trade Exec
Xiaoli Pty Ltd    172.15    3    Trade Exec
Zor Biz Pvt Ltd    84.66    2    Trade Alloc
Zor Biz Pvt Ltd    84.66    1    Trade Alloc

I have no idea, how you are eliminating further records from the set.

FYI - Above query can be constructed in VBA as...
Code: 
    sSQLSting = "Select b.* From (SELECT [Name of Client], [Price], [Label], Count(*) From [Sample Rec Data$] Group by [Name of Client], [Price], [Label]" & _
                " Having Count(*) > 1) as a Left Join [Sample Rec Data$] as b ON a.[Name of Client] = b.[Name of Client] and a.[Price] = b.[Price]" & _
                " and a.[Label] = b.[Label]"

EDIT: Oh, I missed the part on TRADE... just add where clause at the end to eliminate those that does not start with "Trade" using Left()
 
Last edited:
Ok. I tried below with Where clause but it's giving errors also how do my make sure that it takes into consideration the below criteria as well when filtering for only Trade Exec and Trade Alloc row items from label column.

Basically, I want to include the criteria on label column row items, where I want it to filter for only those row items where the Label is = TRADE Exec or Trade Alloc by using "having" and also compare the sum total of Qty column between Trade Exec and Trade Alloc line items so for e.g. if the total sum of Qty column for Trade Exec row items is 10 and Total Sum of Trade Alloc row items is 22 then Macro should take only those rows for which the sum total of Trade Alloc label row items is 10 and copy the same to next sheet along with Trade Exec row items for which the Qty column sum total is also 10.


Code: 
sSQLSting = "Select b.* From (SELECT [Name of Client], [Price], [Label], Count(*) From [Sample Rec Data$] Group by [Name of Client], [Price], [Label]" & _
                " Having Count(*) > 1) as a Left Join [Sample Rec Data$] as b ON a.[Name of Client] = b.[Name of Client] and a.[Price] = b.[Price]" & _
                " and a.[Label] = b.[Label] Where a.[Label] =  '" & *Trade* & "'"
 
* isn't a wild card in SQL, it's '%' and as well, you'll need to use like operator to use wild card.

Code: 
sSQLSting = "Select b.* From (SELECT [Name of Client], [Price], [Label], Count(*) From [Sample Rec Data$] Group by [Name of Client], [Price], [Label]" & _
                " Having Count(*) > 1) as a Left Join [Sample Rec Data$] as b ON a.[Name of Client] = b.[Name of Client] and a.[Price] = b.[Price]" & _
                " and a.[Label] = b.[Label] Where b.[Label] like 'Trade%'"

Alternately you can use...
Code: 
                " and a.[Label] = b.[Label] Where Left(b.[Label], 5) = 'Trade'"

For the last line.
 
Oh, as for your below...
if the total sum of Qty column for Trade Exec row items is 10 and Total Sum of Trade Alloc row items is 22 then Macro should take only those rows
Click to expand...

I'm confused, many of your example does not meet this condition in result set...

Ex:
Altavista Pty Ltd - Trade Exec. Totals 9 in your sample.
Same for Trade Alloc.
 
I have pasted the desired result in second tab .i.e. Sample Filtered Data and if you look at the first example below. Now if you see the sum Total of Qty column for Trade Alloc row items is 9 and for Trade Exec also it's 9. There was one more Trade Alloc row item with Qty value 3 and it was excluded. so basically I want to include only row items where the sum total of Qty column of Trade Alloc row items and Trade Exec row items should be same.

Name of Client Price Qty Label
Altavista Pty Ltd 125 1 Trade Alloc
Altavista Pty Ltd 125 8 Trade Alloc
Altavista Pty Ltd 125 2 Trade Exec
Altavista Pty Ltd 125 5 Trade Exec
Altavista Pty Ltd 125 2 Trade Exec
 
But "HOW" do you define query logic? What you are looking for isn't really suitable for SQL query. What if there's more than one combination that results in same value for Sum of Trade Exec & Trade Alloc? What's the logic in picking one above the other?

For an example, what made you pick 2 + 2 + 5 = 1 + 8 instead of 2 + 2 = 1 + 3?

I assume larger of the two is picked. This sort of trial and error logic isn't suitable for SQL.

Edit: For that matter, coding for this sort of logic is going to be difficult no matter how you do it. As there's bound to be some exception that's going to throw the code off.
 
Last edited:
Agreed, but the price would be different for the row item with value 3 (seems as if i missed to capture that in the sample data) , so it shouldn't be an issue. Also we can also look for sum total of Qty for Trade Exec row items and use it as a base to get the Trade Alloc line items. can this be done.
 
Not really using SQL. You'll need different approach.

Sorry, I'm out of time today. Will see if I can revisit this later this week. If others haven't given you solution by then.
 
can we try below:

If we paste the same data in 2 sheets and compare Trade Alloc rows sumtotal (where price and client name is same) from the first sheet with second sheet where the Trade Exec rows sumtotal is equal to Trade Alloc (where price and client name is same) and then copy matching Trade Alloc rows from first sheet and Trade Exec rows from second sheet into a third.
 
You must log in or register to reply here.
Back
Top