Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

Help with Query in access

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
rfedorov
Asked:
rfedorov
2 Solutions
 
SharathData EngineerCommented:
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
 
als315Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now