?
Solved

SUMIF and Access

Posted on 2003-02-23
6
Medium Priority
?
724 Views
Last Modified: 2012-06-27
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
Comment
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
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 8

Expert Comment

by:heskyttberg
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

by:manchanda
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

by:mherndon
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 8

Expert Comment

by:heskyttberg
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

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

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

Author Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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 …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question