(Solved) – Translating MS access SQL select query to VBA. Breaks when select with Aggregation Sum function

(solved)-–-translating-ms-access-sql-select-query-to-vba.-breaks-when-select-with-aggregation-sum-function

BackGround

I’m trying to use Excel VBA to load data from Microsoft office access database.
The code was always working fine and I am now trying to add an extra column “Position” drawn from the table “EqBucket”in the result table.

I generally, first built the Access SQL code and once its fully working then I translate it into VBA string. The SQL code works fine in Access but now breaks when I translate it back to VBA.
The code seems to break when I add in “Sum(Eq_Buckets.Position) AS PositionOfSum” in either of the select statement a or b.

I’m guess it has to do with the aggregation sum wrapped around the column because this issue has never come up with other direct referenced columns.

Appreciate for any pointers. Thanks

INFO

  1. The result table is joined by two select statement ilas as to a and b.
  2. Database tables Eq_SingleName_LBU, Eq_Buckets << this is where the position data are stored
  3. Eq_Portfolio_Ref is just a reference table which could be ignored

Here is the FULLY working SQL code in Access

Select a.Identifier, a.Issuer, Round(a.MV/1000000,0) AS [MV Date1], Round(b.MV/1000000,0)  AS [MV Date2], Round(a.[MV]-b.[MV],0)/1000000 AS [MV Delta], Round(a.[PositionOfSum]/1000000,0) AS [Position Date1], Round(b.[PositionOfSum]/1000000,0)  AS [Position Date2], Round((a.[PositionOfSum]-b.[PositionOfSum])/1000000,0) AS [Position Delta], a.[Issuer Weight] AS [Issuer Weight  Date1], b.[Issuer Weight] AS [Issuer Weight  Date2], Round(a.[Issuer Weight] - b.[Issuer Weight],4) AS [Issuer Weight Delta], a.Limit AS [Limit Date1], a.[Remaining Limit] AS [Remaining Limit Date1], Round(a.[Issuer Weight]/a.Limit,2) as [Breach Indicator Date1] 
From (SELECT Eq_SingleName_LBU.Identifier AS Identifier, Eq_SingleName_LBU.Issuer AS Issuer, Eq_SingleName_LBU.MV_USD AS MV, Sum(Eq_Buckets.Position) AS PositionOfSum, Eq_SingleName_LBU.Issuer_Weight AS [Issuer Weight], Eq_SingleName_LBU.Test_Limit AS Limit, Eq_SingleName_LBU.Room_Limit AS [Remaining Limit], Eq_SingleName_LBU.Data_Date
FROM Eq_SingleName_LBU INNER JOIN (Eq_Buckets INNER JOIN Eq_Portfolio_Ref ON Eq_Buckets.Composite_Portfolio = Eq_Portfolio_Ref.BBG_Account_Codes) ON Eq_SingleName_LBU.Identifier = Eq_Buckets.BB_UniqueID
Where Eq_Buckets.Data_Date = ([Date_1]) and Eq_SingleName_LBU.UnderTest=”Y”
GROUP BY Eq_SingleName_LBU.Identifier, Eq_SingleName_LBU.Issuer, Eq_SingleName_LBU.MV_USD, Eq_SingleName_LBU.Issuer_Weight, Eq_SingleName_LBU.Test_Limit, Eq_SingleName_LBU.Room_Limit, Eq_SingleName_LBU.Data_Date
HAVING (((Eq_SingleName_LBU.Data_Date) In ([Date_1])))
ORDER BY Eq_SingleName_LBU.Data_Date) AS a LEFT JOIN

(SELECT Eq_SingleName_LBU.Identifier AS Identifier, Eq_SingleName_LBU.Issuer AS Issuer, Eq_SingleName_LBU.MV_USD AS MV, Sum(Eq_Buckets.Position) AS PositionOfSum, Eq_SingleName_LBU.Issuer_Weight AS [Issuer Weight], Eq_SingleName_LBU.Test_Limit AS Limit, Eq_SingleName_LBU.Room_Limit AS [Remaining Limit], Eq_SingleName_LBU.Data_Date
FROM Eq_SingleName_LBU INNER JOIN (Eq_Buckets INNER JOIN Eq_Portfolio_Ref ON Eq_Buckets.Composite_Portfolio = Eq_Portfolio_Ref.BBG_Account_Codes) ON Eq_SingleName_LBU.Identifier = Eq_Buckets.BB_UniqueID
Where Eq_Buckets.Data_Date = ([Date_2]) and Eq_SingleName_LBU.UnderTest=”Y”
GROUP BY Eq_SingleName_LBU.Identifier, Eq_SingleName_LBU.Issuer, Eq_SingleName_LBU.MV_USD, Eq_SingleName_LBU.Issuer_Weight, Eq_SingleName_LBU.Test_Limit, Eq_SingleName_LBU.Room_Limit, Eq_SingleName_LBU.Data_Date
HAVING (((Eq_SingleName_LBU.Data_Date) In ([Date_2])))
ORDER BY Eq_SingleName_LBU.Data_Date) AS b 
ON (a.[Issuer] = b.[Issuer]) AND (a.[Identifier] = b.[Identifier]) 
ORDER BY a.MV DESC;

Here is my VBA translated SQL code

    strSql = " Select a.Identifier, a.Issuer, Round(a.MV/1000000,0) AS [MV Date1], Round(b.MV/1000000,0)  AS [MV Date2], Round(a.[MV]-b.[MV],0)/1000000 AS [MV Delta], Round(a.[PositionOfSum]/1000000,0) AS [Position Date1], Round(b.[PositionOfSum]/1000000,0)  AS [Position Date2], Round((a.[PositionOfSum]-b.[PositionOfSum])/1000000,0) AS [Position Delta], a.[Issuer Weight] AS [Issuer Weight  Date1], b.[Issuer Weight] AS [Issuer Weight  Date2], Round(a.[Issuer Weight] - b.[Issuer Weight],4) AS [Issuer Weight Delta], a.Limit AS [Limit Date1], a.[Remaining Limit] AS [Remaining Limit Date1], Round(a.[Issuer Weight]/a.Limit,2) as [Breach Indicator Date1] " & vbCrLf & _
"FROM (" & vbCrLf & _
"SELECT Eq_SingleName_LBU.Identifier AS Identifier, Eq_SingleName_LBU.Issuer AS Issuer, Eq_SingleName_LBU.MV_USD AS MV, Sum(Eq_Buckets.Position) AS PositionOfSum, Eq_SingleName_LBU.Issuer_Weight AS [Issuer Weight], Eq_SingleName_LBU.Test_Limit AS Limit, Eq_SingleName_LBU.Room_Limit AS [Remaining Limit], Eq_SingleName_LBU.Data_Date" & vbCrLf & _
"FROM Eq_SingleName_LBU INNER JOIN (Eq_Buckets INNER JOIN Eq_Portfolio_Ref ON Eq_Buckets.Composite_Portfolio = Eq_Portfolio_Ref.BBG_Account_Codes) ON Eq_SingleName_LBU.Identifier = Eq_Buckets.BB_UniqueID" & vbCrLf & _
"WHERE Eq_Buckets.Data_Date = (" & Date_1 & ") and ((Eq_SingleName_LBU.UnderTest)=" & Chr(34) & "Y" & Chr(34) & ")" & vbCrLf & _
"GROUP BY Eq_SingleName_LBU.Identifier, Eq_SingleName_LBU.Issuer, Eq_SingleName_LBU.MV_USD, Eq_SingleName_LBU.Issuer_Weight, Eq_SingleName_LBU.Test_Limit, Eq_SingleName_LBU.Room_Limit, Eq_SingleName_LBU.Data_Date " & vbCrLf & _
"HAVING (((Eq_SingleName_LBU.Data_Date) In (" & Date_1 & "))) " & vbCrLf & _
"ORDER BY Eq_SingleName_LBU.Data_Date " & vbCrLf & _
")  AS a LEFT JOIN (" & vbCrLf & _
"SELECT Eq_SingleName_LBU.Identifier AS Identifier, Eq_SingleName_LBU.Issuer AS Issuer, Eq_SingleName_LBU.MV_USD AS MV, Sum(Eq_Buckets.Position) AS PositionOfSum, Eq_SingleName_LBU.Issuer_Weight AS [Issuer Weight], Eq_SingleName_LBU.Test_Limit AS Limit, Eq_SingleName_LBU.Room_Limit AS [Remaining Limit], Eq_SingleName_LBU.Data_Date " & vbCrLf & _
"FROM Eq_SingleName_LBU INNER JOIN (Eq_Buckets INNER JOIN Eq_Portfolio_Ref ON Eq_Buckets.Composite_Portfolio = Eq_Portfolio_Ref.BBG_Account_Codes) ON Eq_SingleName_LBU.Identifier = Eq_Buckets.BB_UniqueID" & vbCrLf & _
"WHERE Eq_Buckets.Data_Date = (" & Date_2 & ") and (((Eq_SingleName_LBU.UnderTest)=" & Chr(34) & "Y" & Chr(34) & "))" & vbCrLf & _
"GROUP BY Eq_SingleName_LBU.Identifier, Eq_SingleName_LBU.Issuer, Eq_SingleName_LBU.MV_USD, Eq_SingleName_LBU.Issuer_Weight, Eq_SingleName_LBU.Test_Limit, Eq_SingleName_LBU.Room_Limit, Eq_SingleName_LBU.Data_Date " & vbCrLf & _
"HAVING (((Eq_SingleName_LBU.Data_Date) In (" & Date_2 & "))) " & vbCrLf & _
"ORDER BY Eq_SingleName_LBU.Data_Date " & vbCrLf & _
")  AS b ON (a.[Issuer] = b.[Issuer]) AND (a.[Identifier] = b.[Identifier]) " & vbCrLf & _
"ORDER BY a.MV DESC;" & vbCrLf & _
""

Leave a Reply

Your email address will not be published. Required fields are marked *