Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SUM IF Expression
- Thread starter Caryisms
- Start date
VictoriaLJones
Technical User
Can you not write an SQL statement group by/sum where appropriate, and then filter the data using the WHERE statements?
Victoria
Victoria
- Thread starter
- #3
I don't think it's Victoria's specificity that needs increasing, it's yours. Are you asking if Access/VBA has functionality similar to Excel's SUMIF and COUNTIF functions?
Columns get summed in a query or with a function that DSUMS(..) based on criteria.
So, yeah, DSUM provides the same general kind of functionality as SUMIF, when you use the appropriate criteria:
Dsum("Salary", "[Workers]", "[Workers].[Dept] = '123'"
will sum the salary for all workers whose department number is 123.
If you provide more specific background, we can be more exact with our answers...
JMH
If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:
More Access stuff at
Columns get summed in a query or with a function that DSUMS(..) based on criteria.
So, yeah, DSUM provides the same general kind of functionality as SUMIF, when you use the appropriate criteria:
Dsum("Salary", "[Workers]", "[Workers].[Dept] = '123'"
will sum the salary for all workers whose department number is 123.
If you provide more specific background, we can be more exact with our answers...
JMH
If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:
More Access stuff at
- Thread starter
- #5
Thank you WildHare. I need to build the DSUM function in a report. The report source is a query with multiple tables. Can you provide an example of the DSUM function which uses more than table? Thank you.
eg. "Table two" Field in Query needs to be added, where "Table one".[Department]="Clinical"
eg. "Table two" Field in Query needs to be added, where "Table one".[Department]="Clinical"
What's in Table 2? A bunch of amounts, with department qualifiers? (how else can we know which amounts to sum up?)
If so, something like this as a column in your query should work:
ClinicalDeptSum: Dsum("Amount", "[Table2]", "[Table1].[DeptID] = 'Clinical'"
You need to have both tables in your query, and joined via the Department identifier in both.
Note the syntax and quote marks. If the join field is TEXT, you need to surround its' literal value with single quote marks, all inside a double-quoted criteria string.
Basically, the DSUM function work like this:
Get me the DOMAIN SUM of [this field] in [this table] where [some field in this table logical op value[/red]
FOO = Dsum("Amount", "tblAmounts", "[tblAmounts]![Account] = 1234[/red]"
If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:
More Access stuff at
If so, something like this as a column in your query should work:
ClinicalDeptSum: Dsum("Amount", "[Table2]", "[Table1].[DeptID] = 'Clinical'"
You need to have both tables in your query, and joined via the Department identifier in both.
Note the syntax and quote marks. If the join field is TEXT, you need to surround its' literal value with single quote marks, all inside a double-quoted criteria string.
Basically, the DSUM function work like this:
Get me the DOMAIN SUM of [this field] in [this table] where [some field in this table logical op value[/red]
FOO = Dsum("Amount", "tblAmounts", "[tblAmounts]![Account] = 1234[/red]"
If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:
More Access stuff at
- Status
Similar threads
- Locked
- Helpful tip
- Locked
- Question
- Locked
- Question
- Locked
- Question