SQL calculating two different things from same data

[baitmaster]

Hi, I'm trying to use SQL within VBA to calculate several related things at once, but I'm doing something wrong

I have 4 columns I'm interested in: ID, Description, Value and Date (see code for exact labels). I want to return a dataset that contains
- unique ID
- unique Description
- sum of all values up to end of this month
- sum of all values up to end of last month

I think I need to create two sets of calculations and then join the results

My current attempt has been to do this but use UNION on the two sets of results, which I can see is wrong. Pretty sure I need to be Joining the results but just getting syntax errors. I've split into 2 separate subqueries for clarity

Function getRsReport1Sum(lAcct As Long, lDate As Long, lDatePrior As Long) As ADODB.Recordset


' create SQL string
Dim strTbl1 As String, strTbl2 As String, strSql As String


strTbl1 = "SELECT " & _
         "[data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS CurrMonth " & _
         "FROM [data$] " & _
         "WHERE [data$].[Account ] = " & lAcct & " " & _
         "AND [data$].[Journal Date ] <= " & lDate & " " & _
         "GROUP BY [data$].[Proj ID], [data$].[Proj Desc] "
         
strTbl2 = "SELECT " & _
         "[data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS PriorMonth " & _
         "FROM [data$] " & _
         "WHERE [data$].[Account ] = " & lAcct & " " & _
         "AND [data$].[Journal Date ] <= " & lDatePrior & " " & _
         "GROUP BY [data$].[Proj ID], [data$].[Proj Desc] "
         
strSql = "SELECT * FROM (" & _
         strTbl1 & _
         "UNION " & _
         strTbl2 & _
         ");"

The results I'm getting are in 3 columns so clearly the values are being merged, which is not what I want - I'm trying to keep two separate columns

The final SQL string I'm getting is
SELECT *
FROM (

SELECT [data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS CurrMonth
FROM [data$]
WHERE [data$].[Account ] = 120009 And [data$].[Journal Date ] <= 43585
GROUP BY [data$].[Proj ID], [data$].[Proj Desc]

UNION

SELECT [data$].[Proj Desc], [data$].[Proj ID], SUM([data$].[Monetary AMT ]) AS PriorMonth
FROM [data$]
WHERE [data$].[Account ] = 120009 And [data$].[Journal Date ] <= 43555
GROUP BY [data$].[Proj ID], [data$].[Proj Desc]

);

 

[Joe4]

OK, I tried doing a real simple example in Access, where I had one query for ID, and three for the months, and here is the SQL code it came up with:

SELECT [qry1-IDs].ID, Nz([qry2-Month1]![SumOfAmount],0)+0 AS Month1, Nz([qry3-Month2]![SumOfAmount],0)+0 AS Month2, Nz([qry4-Month3]![SumOfAmount],0)+0 AS Month3
FROM [B][COLOR=#ff0000](([/COLOR][/B][qry1-IDs] 
LEFT JOIN [qry2-Month1] 
ON [qry1-IDs].ID = [qry2-Month1].ID[B][COLOR=#ff0000])[/COLOR][/B] 
LEFT JOIN [qry3-Month2] 
ON [qry1-IDs].ID = [qry3-Month2].ID[B][COLOR=#ff0000])[/COLOR][/B] 
LEFT JOIN [qry4-Month3] 
ON [qry1-IDs].ID = [qry4-Month3].ID;

Pay special attention to the parentheses I have highlighted in red. So maybe you need to structure/nest the code like that.

 

[baitmaster]

YES

Ahem I mean, yes, thanks again. I was sure it had something to do with parentheses and by confirming this I was able to get it after a few more attempts. Another useful trick added to the Excel toolkit :D

 

[Joe4]

You are welcome!

Glad we figured it out and got it working!