Adding rows of data in MS Access using multiple criteria

Question

I'm new to Access. I figured out how to create a query that sums data from rows in a table using INNER JOIN / GROUP, using one set of criteria for adding.

The first 3 columns in attachment (in grey) are from the table ("Sheet1"), the 4th column was generated using the SQL code I show at the bottom. This 4th column, YTD Sales_by_Acct, adds up Sales for all Accts with the same Acct number.
How would I generate the 5th column in the attached, where Sales are added (a) for all Accts with the same Acct number (per the 4th column), (b) for all Months <= the month specified in the 2nd column?

Image of small table, please click on below link

Here's the SQL code I used to generate the 4th column, Sales_by_Acct:

SELECT XX.*, Sales_by_Acct 
FROM Sheet1 AS XX 
     INNER JOIN 
        (SELECT [Acct], SUM([Sales]) AS Sales_by_Acct 
        FROM Sheet1 
        GROUP BY [Acct]) 
     AS groupSales_by_Acct ON XX.[Acct] = groupSales_by_Acct.[Acct];

Jan · Accepted Answer · 2018-05-14 15:47:07Z

1

You're adding report columns which you can do by sub queries within the select rather than joins to the main table (Sheet1).

Here's an example that I believe will meet your need:

    SELECT *, (SELECT SUM([Sales]) FROM Sheet1 WHERE Sheet1.Acct = S1.Acct) AS groupSales_by_Acct, 
              (SELECT SUM([Sales]) FROM Sheet1 WHERE Sheet1.Acct = S1.Acct AND Sheet1.Month <= S1.Month) AS YTDSalesByAcct
    FROM Sheet1 S1

answered May 14, 2018 at 15:47

Jan

4215 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parfait Over a year ago

While the correlated subquery is needed, OP's original is actually more efficient since you are not running SUM(Sales) for every row but only once!

Collectives™ on Stack Overflow

Adding rows of data in MS Access using multiple criteria

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related