MS Access Query - Count across Multiple Fileds

Posted on 2009-05-12
Medium Priority
Last Modified: 2012-05-06
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

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


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;

Open in new window


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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

765 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