Solved

Access SQL to create distinct with indicators

Posted on 2016-07-28
21
58 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
User Level Security 6 38
ms access filter query with empty combobox 5 29
MS access 2010. Trouble on exporting to excel 2010 13 8
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 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