asked on

Is there a way to do the equivalent of Excel's Sumifs() in an Access Expression (or VBA)

I have a table in Access with information (see attached). I'm trying to figure out if there is a way to get the sum of a column (field) where a certain criteria in two other fields is met. For example, I'd like to get year to date (YTD) totals for a given account. So, I'd need the total sales for a given account (criteria #1) for all months between Jan. and the current records Month. Does anyone know if there is a way to do this in Access?
Sumifs-AccessEquivalent.xlsx

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

Probably pretty close, but I'd like they date criteria to be for any date on or before the date of the current record. How would that change the code? Also, that is a SQL statement. Is there an expression that I could use in a a query to add a field with that data?

Missus Miss_Sellaneus

you need something like this..

SELECT acct, SUM(IIF(Month >= january AND Month <= currentmonth, Sales, 0)) AS YTDSales
FROM table1
HAVING YTDSales <> 0

BBlu

ASKER

Thank you very much. But that would be the sql for a query, right? Is there a way to do it in an expression.

SOLUTION

Missus Miss_Sellaneus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

I'm actually trying to reproduce an excel file our sales team has been manually doing for years. We have all of the data in two access tables. For each account we have a matrix with columns for all months in the current period. We then have rows for Sales, YTD sales, Payments, YTD Payments. (If I can figure out one, I can do both; that's why I didn't mention the pmts. part). Anyhow, I was thinking I could mimic the excel file by:

1. creating a query and adding a field/column for the YTD total for each record. For example, if the record has:
a. Account= 1234
b. Month=3/1/10 (March 2010)
c. Sales= $340
I could add a field
d. YTDSales = $640 (if Jan was $200 and Feb was $100, for example)

2. I could then do a Union Query something like
Select "Sales" as Category, Account, Month, Sales from SalesTable
Union All
Select "YTD" as Category, Account, Month, YTDSales from SalesQuery

And if I I had a table that ordered the 'Category' Field, I could reproduce what I'm going after (see attached)
DesiredReport.xlsx

BBlu

ASKER

I almost figured it out..I guess the real question is:
Is there a way to have multiple criteria in DSum?

DSum("UnitPrice", "Order Details", "OrderID = 10248")

BBlu

ASKER

I figured out that you can use "AND" to add multiple criteria. But I did not know how to do it in SQL either, so you helped. Thanks.

GRayL

Thanks, glad to help.