[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access SQL to create distinct with indicators

Posted on 2016-07-28
21
Medium Priority
?
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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
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.

 
LVL 41

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 143

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 143

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 143

Expert Comment

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

Author Comment

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

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 41

Expert Comment

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

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 143

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 143

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

649 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