[Webinar] Streamline your web hosting managementRegister Today

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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