Solved

MS Access Query - Count across Multiple Fileds

Posted on 2009-05-12
3
480 Views
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





0
Comment
Question by:jfwhite4
3 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 63 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;

Open in new window

0
 
LVL 6

Assisted Solution

by:BALMUKUND KESHAV
BALMUKUND KESHAV earned 62 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now