BBlu
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you need something like this..
SELECT acct, SUM(IIF(Month >= january AND Month <= currentmonth, Sales, 0)) AS YTDSales
FROM table1
HAVING YTDSales <> 0
SELECT acct, SUM(IIF(Month >= january AND Month <= currentmonth, Sales, 0)) AS YTDSales
FROM table1
HAVING YTDSales <> 0
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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")
Is there a way to have multiple criteria in DSum?
DSum("UnitPrice", "Order Details", "OrderID = 10248")
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.
Thanks, glad to help.
ASKER