Tech Support Guy banner
Status
Not open for further replies.

Solved: Access 2007 - Query to count records with multiple requirements

2K views 8 replies 2 participants last post by  OBP 
#1 ·
Our environmental nonprofit has several projects, and most projects have either a marine or terrestrial conservation area. Some have neither, and have therefore been marked as "no conservation area."

Some projects also have multiple conservation areas, and they might be of the same type. So I have a table like the following:

Project ID: Conservation Category:
1 Marine
1 Marine
1 Terrestrial
2 Neither
3 Terrestrial
3 Terrestrial
4 Marine

Sorry if that's hard to read - the first column is supposed to be numerical project ID's and the second shows which type of conservation category it has.

So I need to make a query that'll give me the number of projects with Marine, Terrestrial, Both, or Neither conservation categories. But I have no idea how to approach this, since I don't know how to get a query to recognize the "Both" condition.

The query should give the following result for the example above:

Conservation Category: Count:
Marine 2
Terrestrial 2
Both 1
Neither 1

Explanation: Project 1 and 4 have marine components. Projects 1 and 3 have terrestrial components. Only project 1 has both a marine and terrestrial component. Only project 2 has has "neither" component.
 
See less See more
#3 ·
Hm, you're right, I think this would give us more useful data if it gave us the following result:

Conservation Category: Count:
Marine 1
Terrestrial 1
Both 1
Neither 1

That seems like it's going to be even harder, since we have to make it so the query not only recognizes when to increase the "both" count by 1, but when to decrease the Marine and Terrestrial counts by 1 each to compensate.

Although it definitely makes more sense, since the count will add up to the total number of projects.
 
#4 ·
Back to your question, you know the other grouping query we created if you use one like that and get a value of 2 for the Category then it must be Both.
So you just need an extra column that says something like
iif([CountofCategory] = 2,"Both",[Category]
To do this you may have to create another query that combines the original data and the count.
 
#6 ·
Unbelievable. It worked. It took 4 queries, one based on another based on another... based on two tables. But it worked. Amazing. Thank you so much.

I'm going to mark this solved, and turn these queries into charts. Speaking of charts, if you could help me with this formatting issue, it would be very appreciated.

Thanks again OBP.
 
#8 ·
I definitely agree, but all my data is in Access. As a matter of fact, the Access database I've been building is replacing an Excel "database" that was a total mess. So far, it's been a good decision. I just hope I can figure out how to model this data visually... maybe I'll have to export it to Excel, but what a pain that would be.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top