Solved

Access SQL to create distinct with indicators

Posted on 2016-07-28
21
55 Views
Last Modified: 2016-08-01
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
Comment
Question by:GNOVAK
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41733383
 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
 
LVL 14

Expert Comment

by:wsh2
ID: 41733418
SELECT T1.[Name], [Indicator]
  FROM [MyTable]
 WHERE [Indicator] = "X"
 UNION
SELECT [Name], "^"
  FROM [MyTable]
 WHERE [Indicator] <> "X"

Open in new window

0
 

Author Closing Comment

by:GNOVAK
ID: 41733444
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
 
LVL 40

Expert Comment

by:Sharath
ID: 41733449
Do you have any other indicators other than X or a blank or NULL value?
0
 

Author Comment

by:GNOVAK
ID: 41733457
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41733475
did you really remove the indicator from the group by?
0
 

Author Comment

by:GNOVAK
ID: 41733520
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41733548
is the blank a null or really an empty string?
0
 

Author Comment

by:GNOVAK
ID: 41733567
looks like an empty string - I had to do a Len(trim(indicator))=0
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41733584
can you show the output with min and max values, please
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:GNOVAK
ID: 41733720
sure - the output :
Name      Indicator
John        ^
Mike      X
Sue        X
Joe       ^
Sally     ^
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41733743
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
 

Author Comment

by:GNOVAK
ID: 41733788
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
 
LVL 40

Expert Comment

by:Sharath
ID: 41733868
Provide your input data where it is breaking.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41733906
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
 

Author Comment

by:GNOVAK
ID: 41734747
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41735234
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
 

Author Comment

by:GNOVAK
ID: 41735241
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
 

Author Comment

by:GNOVAK
ID: 41735253
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41737009
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
 

Author Comment

by:GNOVAK
ID: 41737991
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now