Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with Query in access

Posted on 2013-11-01
2
Medium Priority
?
145 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 41

Accepted Solution

by:
Sharath earned 1600 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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

916 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