Solved

# SUMIF and Access

Posted on 2003-02-23
Medium Priority
724 Views
I'm new to most of this, so forgive me if this is a stupid question.  Is there any kind of an equivalent to Excel's SUMIF function that can work in Access?

An example of what I'm wanting to do would be a bank savings account.  In the table might be transactions labeled as deposits, withdrawls, or interest deposits.  Would it be possible to have a sum of all deposits (ignoring withdrawls) WITHOUT having to have them grouped?  In other words, I would like transactions sorted by date, but I would like a sum by type.
0
Question by:mherndon
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2

LVL 8

Expert Comment

ID: 8002306
Hi!

If your transaction table look like this.

Let's say 1 = Deposit, 2 = withdraw, 3 = interest deposit for the tranType column.

bankacct, tranType, amount, date

You can do like this:
select sum(amount) from trantable where (tranType = 1 OR tranType = 3) and bankacct = 'xxxx';

Now you get that kind of sum for bank account xxx.
If you want to do this for all bank account you need to use group by and having statement.

Regards
/Hans - Erik Skyttberg

0

LVL 2

Expert Comment

ID: 8002335
SELECT Sum(deposit)
FROM Table7
WHERE type='a'

or

SELECT Sum(Table7.deposit) AS SumOfdeposit
FROM Table7
WHERE (((Table7.type)='a'))
GROUP BY Table7.date
ORDER BY Table7.dated;
0

Author Comment

ID: 8006647
OK, I got that to work as a query.  I was more specifically interested in a report, though, and I can't get that to work.

Using our example, the report is grouped by customer and contains all of their transactions on a page.  At the bottom is "=SUM(Amount)" which calculates their balance.  I'd also like to include Total Deposits and Total Withdraws.

I tried (SELECT SUM(Amount) WHERE [Type]='deposit'), since it's already bound to a table and to the customer grouping, but it returns #Name?.
0

LVL 8

Expert Comment

ID: 8009283
Hi!

I'm not too familiar with Access, good with SQL though, as far as I know when you use aggregate functions like sum, min, max, avg and so on you always need a group by statement.

So your select should look like this:
SELECT sum(amount) FROM myTable GROUP BY [Type] HAVING [Type] = 'deposit';

Regards
/Hans - Erik Skyttberg
0

LVL 2

Accepted Solution

manchanda earned 200 total points
ID: 8010483
u can try like this:

=Sum(IIf([Type]="a",[deposit],0))
0

Author Comment

ID: 8011232
That worked EXACTLY as I wanted it to.  Thanks for all your help - both of you!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll