Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

MS Access Query - Count across Multiple Fileds

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
jfwhite4
Asked:
jfwhite4
2 Solutions
 
FlysterCommented:
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;

Open in new window

0
 
BALMUKUND KESHAVCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now