Solved

Help with Query in access

Posted on 2013-11-01
2
139 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
2 Comments
 
LVL 40

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 39

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Make query more efficient 1 21
What query can i write to find where a function is 4 35
Run SQL Server Proc from Access 11 31
Mysql Left Join Case 10 56
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

803 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