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?
GNOVAKAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.