• Status: Solved
• Priority: Medium
• Security: Public
• Views: 743

# SUMIF and Access

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
mherndon
• 2
• 2
• 2
1 Solution

Commented:
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

Commented:
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 Commented:
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

Commented:
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

Commented:
u can try like this:

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

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

## Featured Post

• 2
• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.