Solved

Help with Query in access

Posted on 2013-11-01
2
143 Views
Last Modified: 2013-11-21
I have  table
Grouping      Indicator      Count      CountContracts      Savings
Status A      #      4      7                     1.3
Status A      X      7      10                     3.8
Status B      #      8      2                     6
Status B      X      5      1                     0.5
Status C      #      6      3                     21.75
Status C      X      6      5                     4.092


I need a query which should give me the number of :

Count for Status A and B only, Saving for A and B only, Count for # for A and B only, Saving for # for A and B only
It should be :
24,     11.6,           9,          7.3
0
Comment
Question by:rfedorov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 400 total points
ID: 39618067
try this.
SELECT SUM([Count]) AS AB_Count,
       SUM([Savings]) AS AB_Savings,
	   SUM(IIF([Indicator] = '#',[CountContracts],0)) AS [AB#_CountContracts],
	   SUM(IIF([Indicator] = '#',[Savings],0)) AS [AB#_Savings]
  FROM your_table
 WHERE [Grouping] IN ('Status A','Status B')

Open in new window

0
 
LVL 40

Assisted Solution

by:als315
als315 earned 100 total points
ID: 39618618
You can also add some table, where your groups will be combined to supergroups. For your sample it will be:
Grouping           SuperGroup
Status A              SuperG1
Status B              SuperG1
Status C              SuperG2
Add this table to your query and you will be able to filter by this supergroup. It is very helpful if you use pivot tables.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

624 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