Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# MS Access Query - Count across Multiple Fileds

Posted on 2009-05-12
Medium Priority
500 Views
I'm trying to create a query in MS Access that provides a count for multiple field of the data that fints into certain ranges.

Table looks like this: (dummy data)

budget, actual, estimated, proposed
\$50, \$150, \$160, \$155
\$40, \$350, \$140, \$255
\$250, \$250, \$110, \$115
\$450, \$250, \$260, \$55

The query result should look like this:  Or it could have the ranges across the top.
The query gives a count for each field of the values that meet the criteria on the left.

RANGE, budget, actual, estimated, proposed

<=\$100:        2, 0, 0, 1
>\$100 & <=\$200:      0,0,2,2
>\$200 & <=\$300:      1,2,1,1
>\$300:       1,1,0,0

0
Question by:jfwhite4
[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

LVL 22

Accepted Solution

Flyster earned 252 total points
ID: 24370005
You can make 4 separate queries, one for each category, using the attached code.

Flyster

``````SELECT Sum(IIf([Budget]<=100,1,0)) AS [<=\$100], Sum(IIf([Budget] Between 101 And 200,1,0)) AS [>\$100 & <= \$200], Sum(IIf([Budget] Between 201 And 300,1,0)) AS [>\$200 & <=\$300], Sum(IIf([Budget]>300,1,0)) AS [>\$300]
FROM YourTable;
``````
0

LVL 6

Assisted Solution

BALMUKUND KESHAV earned 248 total points
ID: 24371992
Here is final query which you are looking for:

SELECT Sum(IIf([Budget]<=100,1,0)) AS [<=Budget\$100], Sum(IIf([Budget] Between 101 And 200,1,0)) AS [Budget>\$100 & <= \$200], Sum(IIf([Budget] Between 201 And 300,1,0)) AS [Budget>\$200 & <=\$300], Sum(IIf([Budget]>300,1,0)) AS [Budget>\$300], Sum(IIf([actual]<=100,1,0)) AS [<=Actual\$100], Sum(IIf([Actual] Between 101 And 200,1,0)) AS [Actual>\$100 & <= \$200], Sum(IIf([Actual] Between 201 And 300,1,0)) AS [Actual>\$200 & <=\$300], Sum(IIf([Actual]>300,1,0)) AS [Actual>\$300], Sum(IIf([estimated]<=100,1,0)) AS [<=estimated\$100], Sum(IIf([estimated] Between 101 And 200,1,0)) AS [estimated>\$100 & <= \$200], Sum(IIf([estimated] Between 201 And 300,1,0)) AS [estimated>\$200 & <=\$300], Sum(IIf([estimated]>300,1,0)) AS [estimated>\$300], Sum(IIf([Proposed]<=100,1,0)) AS [<=Proposed\$100], Sum(IIf([Proposed] Between 101 And 200,1,0)) AS [Proposed>\$100 & <= \$200], Sum(IIf([Proposed] Between 201 And 300,1,0)) AS [Proposed>\$200 & <=\$300], Sum(IIf([Proposed]>300,1,0)) AS [Proposed>\$300]
FROM YourTable;

Bm Keshav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month9 days, 13 hours left to enroll