Solved

MS Access Query - Count across Multiple Fileds

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

825 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