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

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
Asked:
mherndon
  • 2
  • 2
  • 2
1 Solution
 
heskyttbergCommented:
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
 
manchandaCommented:
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
 
mherndonAuthor 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
heskyttbergCommented:
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
 
manchandaCommented:
u can try like this:

=Sum(IIf([Type]="a",[deposit],0))
0
 
mherndonAuthor Commented:
That worked EXACTLY as I wanted it to.  Thanks for all your help - both of you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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