[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Access SQL to create distinct with indicators

I have the following data:
ID      Name      Indicator
23      John      
43      Mike      X
50      John      X
12      Sue              X
14      Joe      
76      Sally      
34      Joe      
55      John      X
 
By doing a query (select name, count(ID), indicator from TABLE group by name, indicator) I am able to get this:
Name      Count      Indicator
John      1      
John      2      X
Mike      1      X
Sue              1      X
Joe              2      
Sally      1      

But I need to collapse it further and give it a unique indicator if the name is found with a 'X' and without a 'X'. if so, give it a '^' indicator
So the end result should look like this:
Name      Indicator
John      ^
Mike      X
Sue              X
Joe      
Sally      

How would I do this in a query?
0
GNOVAK
Asked:
GNOVAK
  • 10
  • 8
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
 what about this
select name, count(ID)
, case when min(indicator) = max(indicator)
then min(indicator) else '^' end indicator
from TABLE group by name
0
 
wsh2Commented:
SELECT T1.[Name], [Indicator]
  FROM [MyTable]
 WHERE [Indicator] = "X"
 UNION
SELECT [Name], "^"
  FROM [MyTable]
 WHERE [Indicator] <> "X"

Open in new window

0
 
GNOVAKAuthor Commented:
Actually, the Case statement gave me an error, so I used IIF() instead and it worked like a charm.
Appreciate the suggestion, it took me on the right course
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
SharathData EngineerCommented:
Do you have any other indicators other than X or a blank or NULL value?
0
 
GNOVAKAuthor Commented:
Actually, it didnt work.
It gave me either X or ^  when I really needed X, ^ or blank.
Blank if they are all blank
X if they are all X
^ if they are X and blank for the same name
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you really remove the indicator from the group by?
0
 
GNOVAKAuthor Commented:
there are only two in the original : "X" ,  blank
and the result should have three "X", Blank, "^"

indicator was removed in the group by. Keep in mind, I have to use the
iif(min(indicator)=max(indicator), min(indicator), '^')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the blank a null or really an empty string?
0
 
GNOVAKAuthor Commented:
looks like an empty string - I had to do a Len(trim(indicator))=0
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the output with min and max values, please
0
 
GNOVAKAuthor Commented:
sure - the output :
Name      Indicator
John        ^
Mike      X
Sue        X
Joe       ^
Sally     ^
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then I think the issue is likely different indicator values...

select name, count(ID)
, iif(min(trim(indicator)) = max(trim(indicator))
then min(indicator) else '^' end indicator
from TABLE
group by name
0
 
GNOVAKAuthor Commented:
I tried that as well.  When I look at the IIF() statement compare it is comparing the X and empty string correctly. It is comparing the two X's correctly, but it is not seeing two empty strings correctly. And that's even if I place a trim() around the indicator
0
 
SharathData EngineerCommented:
Provide your input data where it is breaking.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please provide the output of


select name, count(ID)
, iif(min(trim(indicator)) = max(trim(indicator))
then min(indicator) else '^' end indicator
, min(indicator)
, max(indicator)
, len( min(indicator))
, len (max(indicator))
from TABLE 
group by name
0
 
GNOVAKAuthor Commented:
This file should help.
Guy - the query is in the database below.
I didnt put the length in this one - but when I did, the length of the "empty" indicators was also "empty", so I'm thinking it is null.

Still stumped.
test.zip
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then this should do:
select name, count(ID)
, iif( min(trim(nz(indicator, ' ') )) = max(trim( nz(indicator ,  ' ') )) 
then min(indicator) else '^' end indicator
from TABLE 
group by name 

Open in new window

0
 
GNOVAKAuthor Commented:
same problem. Using the test data in the zip file, Erin should show up as a '' , instead he shows up with a '^'.
There are three potential end values:
''         - if both are "blank" or whatever they are in the above
'^'       - if one of each exists with the same name
'X'       - if both are X

the SQL is apply '^' to both of the first cases and I cant figure out how to get it to show ''
0
 
GNOVAKAuthor Commented:
if I try to see what value is in there by using a asc(indicator) or asc(trim(indicator)), it gives me a #Func!
and that is not trapable it appears. I cant even do a iif(asc(indicator)='#Func!'))

still totally confused.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't have ms access on my private laptop, so I cannot test that file for now ...
and there is no sql fiddle with ms access, so I am "blind" for the time being
yet another suggestion
select name, count(ID)
, iif( min(trim(nz(indicator, 'x') )) = max(trim( nz(indicator ,  'x') )) 
then min(indicator) else '^' end indicator
from TABLE 
group by name 

Open in new window

0
 
GNOVAKAuthor Commented:
Here's what wound up working for the iif statement.
(notice the space between the single quotes):
iif(min(IIf(Len(Trim(indicator))>0,indicator,' ')) = max(IIf(Len(Trim(indicator))>0,indicator,' ')),min(IIf(Len(Trim(indicator))>0,indicator,' ')),'^')
0

Featured Post

Independent Software Vendors: 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!

  • 10
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now