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

# 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
2 Solutions

Commented:
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

Commented:
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.