By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,190 Members | 2,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,190 IT Pros & Developers. It's quick & easy.

I want to Add Multiple Criteria in an Access Report

P: 2
I have a database where I have a field (Disposition) that allows you to choose options from a drop down menu, and I want to count each of those options in a Report. How can I do this?

I've tried the following:

Expand|Select|Wrap|Line Numbers
  1. =SUM(IIF([Disposition] like "Partial Grant", 1, 0))
Expand|Select|Wrap|Line Numbers
  1. =Count(IIF([Disposition] like "Partial Grant", 0))
I keep getting a large number. I think it's because it's double counting the some of the Disposition Results. Please help?

-Julio
Dec 18 '12 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,397
Julio1974 , Welcome to Bytes!

This would be something best handled in an aggregate or cross-tab query. And then there is the crosstab query that should be able to handle the same thing. In this case you're preference.

You should post your entire SQL - Please format it using the code button and you should read the posting guidlines as to how to post the SQL.

You might consider reading thru Aggregate Query Woes as some of the pitfalls are pointed out here.

Here's Mr. Brown's site... a ton of tips so I'd bookmark this site! However, in this case the cross-tab: Crosstab query techniques

Some basic SQL stuff - : Queries in Access using SQL View

Another mention of the cross-tab; however, this will help with some with the report side: Dynamic Reports

That should be enough to get you started. Rabbit, Neopa, and/or others should be along shortly (if they haven't already posted while I was typing this ... they type faster than I do :-P )

Once you get your query/report put together, should you still be having issues, please read the FAQ and posting guidelines about how to post your SQL/Code and will take another poke at the problem.
Dec 18 '12 #2

P: 2
Zmbd, thanks for the quick response! As you can tell, I'm not a programmer. I'm just attempting to take on the challenge of automating a report I have to submit annually, and it has a lot of basic summation, as well as other more complicated stats (median's, averages, etc).

I'll read through your suggested sites.

Thanks again,
Julio
Dec 18 '12 #3

NeoPa
Expert Mod 15k+
P: 31,433
Julio, your first attempt seems fine to me. There is little point in using the Like operator when = will do just as well, but this won't actually cause any problems.

If the results are not as expected then you need to look at what you are expecting and why. Unfortunately, with the limited info available to us, there's little we can help with on that score.
Dec 19 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.