Solved

Displaying DISTINCT Values using SQL, Access or Pivot table

Posted on 2013-11-22
25
341 Views
Last Modified: 2013-12-01
I have a list of Insurance Polices with some additional data. I skinnied it down. The top list represents the current data and the bottom list would be the results.

I'm not sure the best way. Open to excel pivot table help.

If in Access or SQL, then I would need more code/query help.
EE-Carrier-Load.xlsx
0
Comment
Question by:Newbi22
  • 11
  • 9
  • 3
  • +1
25 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39670540
An easy way is to add a column with the following formula, then filter on 0 values. This will give you the required list.

=COUNTIFS($A$2:$A$15,A2,$D$2:$D$15,"<>"&$D$2)

Thomas
0
 

Author Comment

by:Newbi22
ID: 39670668
Help me understand the formula a little better. Mainly the last part.


I think I get the first 3 parts, but I am a little confused on how it returns a 0 (zero). Also explain the syntax of the formula becuase I will need to take this and expand it to other areas of the data. The data consist of over 5,000 lines.
0
 

Author Comment

by:Newbi22
ID: 39670679
Also, is there another way to complete this using SQL?? Because I have to do this monthly and sometimes the extracting becomes a pain.

--Michael
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39670693
The formula counts the number of lines that have that customer number and are not equal to West. I've used D2 cause West was in there, you could hard code it to

=COUNTIFS($A$2:$A$15,A2,$D$2:$D$15,"<>West Business")

In SQL, I'd do it in a couple layers, something like:

SELECT Customer, code, policy No 
FROM My Table
WHERE Customer NOT IN (SELECT DISTINCT Customer WHERE Carrier <>'West Business') 

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 39670699
give a try
SELECT *
  FROM YourTable AS t1
 WHERE EXISTS ( SELECT 1
				  FROM (SELECT [Account  Name],[Cust  No],
							   COUNT(*) Cnt,	
							   SUM(IIF([Parent Co] LIKE 'West Bend%',1,0)) Sum_Parent
						  FROM (SELECT DISTINCT [Account  Name],[Cust  No],[Parent Co]
								  FROM YourTable) AS t2
						 GROUP BY [Account  Name],[Cust  No]) AS t3
				 WHERE Parent_Sum = 1 AND Cnt = 1
				   AND t1.[Account  Name] = t3.[Account  Name]
				   AND t1.[Cust  No] = t3.[Cust  No])

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39670738
try this query


SELECT A.*
FROM yourTable AS A
WHERE (((Exists (select [account name] from yourTable B where B.[account name] = A.[account name] and B.[Parent Co] <> 'West Bend Mutual Ins. Co.' ))=False));
0
 

Author Comment

by:Newbi22
ID: 39676261
Hello,

I think that I have this now almost completed, but how do I take this.....

The formula counts the number of lines that have that customer number and are not equal to West. I've used D2 cause West was in there, you could hard code it to

=COUNTIFS($A$2:$A$15,A2,$D$2:$D$15,"<>West Business")

Open in new window


and change it to:

<> West Business and <>North Business and  Central Business.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39676283
If you have a customer that has both west and north, do you want to keep it? Or do you just want to keep customers that only have north, and customers that only have west?

Thomas
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39676425
@Newbi22

the sql statement i posted above at http:#a39670738 will give you the result you want, using an access table.
0
 

Author Comment

by:Newbi22
ID: 39676470
I added another column to classifying the carrier type. They are either standard or E&S.

I want all the carriers that are:

Standard, but not West Bend or Hanover.

Overall trying to get 4 groups....

1. Customers the only have All West Bend Policies - Got them
2. Customers that only have All Hanover Polices - Got them
2. All E&S (got them)
3. All Standard type that are NOT exclusive West Bend or Hanover.
    For example, I want a customer that has a carrier equal to standard, their policies can be anyone except results from #1 and #2 above
0
 

Author Comment

by:Newbi22
ID: 39678528
This is not working.....

SELECT Small_Commercial_Buckets.[Account  Name], Small_Commercial_Buckets.[Cust  No], Small_Commercial_Buckets.[Policy No], Small_Commercial_Buckets.[Pol Exec], Small_Commercial_Buckets.[Pol Rep], Small_Commercial_Buckets.[Commission], Small_Commercial_Buckets.[Premium], Small_Commercial_Buckets.[Market], Small_Commercial_Buckets.[Parent Co], Small_Commercial_Buckets.[Policy  Status]

FROM Small_Commercial_Buckets



WHERE Small_Commercial_Buckets.[Account  Name] NOT IN (SELECT DISTINCT Small_Commercial_Buckets.[Account  Name] WHERE Small_Commercial_Buckets.[Parent Co] <>' West Bend Mutual Ins. Co.') ;

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39678568
:-0
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Newbi22
ID: 39678622
I am receiving some syntax error;

      
com.entrinsik.informer.exception.SqlQueryException: [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'Small_Commercial_Buckets.[Account Name] NOT IN (SELECT DISTINCT Small_Commercial_Buckets.[Account Name] WHERE Small_Commercial_Buckets.[Parent Co] <>' West Bend Mutual Ins. Co.')'.: Exception class=java.sql.SQLException Exception message=[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'Small_Commercial_Buckets.[Account Name] NOT IN (SELECT DISTINCT Small_Commercial_Buckets.[Account Name] WHERE Small_Commercial_Buckets.[Parent Co] <>' West Bend Mutual Ins. Co.')'. Exception SQL State=37000 Exception vendor error code=-3100

In access I get.....


Syntax error in query expression
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39679593
Did you try my query?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39679663
did you try my query? at http:#a39670738
0
 

Author Comment

by:Newbi22
ID: 39679693
by: capricorn1 and by: Sharath_123...... thanks for the help, but I struggle with your codes and trying to plug my data. Sometimes I do not think I get it right and that is my lack of understanding. I tried both and probably not doing it right.  Here is the code I created with my table.

I apolgize for needing so much help. I'm an accountant, not in IT.. I try...
Capricorn1.docx
Sharath-123.docx
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39679745
Copy and paste this


SELECT Small_Commercial_Buckets.[Account  Name],
Small_Commercial_Buckets.[Cust  No],
Small_Commercial_Buckets.[Policy No],
Small_Commercial_Buckets.[Pol Exec],
Small_Commercial_Buckets.[Pol Rep],
Small_Commercial_Buckets.[Commission],
Small_Commercial_Buckets.[Premium],
Small_Commercial_Buckets.[Market],
Small_Commercial_Buckets.[Parent Co],
Small_Commercial_Buckets.[Policy  Status]
FROM Small_Commercial_Buckets
WHERE (((Exists(Select Small_Commercial_Buckets.[Account  Name] from Small_Commercial_Buckets AS B where B.[account name] = Small_Commercial_Buckets.[Account Name] and B.[Parent Co] <> 'West Bend Mutual Ins. Co.' ))=False));


OR this one using Aliases

SELECT A.[Account  Name],
A.[Cust  No],
A.[Policy No],
A.[Pol Exec],
A.[Pol Rep],
A.[Commission],
A.[Premium],
A.[Market],
A.[Parent Co],
A.[Policy  Status]
FROM Small_Commercial_Buckets AS A
WHERE (((Exists(Select [Account  Name] from Small_Commercial_Buckets AS B where B.[account name] = A.[Account Name] and B.[Parent Co] <> 'West Bend Mutual Ins. Co.' ))=False));


.
.
0
 

Author Comment

by:Newbi22
ID: 39688332
OK. I got back to this after coming out if a Turkey food coma....Here is the scoop.... I am not sure I explained this correct to yield the results... I put this in Access and it is asking for 2 prompt's on Account Name. Not sure why that is needed..I am assuming it comes from the where clause...

WHERE (((Exists(Select [Account  Name] from Small_Commercial_Buckets AS B where B.[account name] = A.[Account Name] and B.[Parent Co] <> 'West Bend Mutual Ins. Co.' ))=False));

I am looking to return all the select fields where the Carrier is ONLY West Bend Mutual Ins. Co. If the Account Name has a Carrier of Acuity and West Bend Mutual. I don't want to see that information from the select statement.
0
 

Author Comment

by:Newbi22
ID: 39688392
Sharath_123.........

I tried to plug in the necessary info, but getting an error in MS Access..

Syntax Error (missing operator) in query submission 'Exists (Select 1................

SELECT Small_Commercial_Buckets.[Account  Name],
Small_Commercial_Buckets.[Cust  No],
Small_Commercial_Buckets.[Policy No],
Small_Commercial_Buckets.[Pol Exec],
Small_Commercial_Buckets.[Pol Rep],
Small_Commercial_Buckets.[Commission],
Small_Commercial_Buckets.[Premium],
Small_Commercial_Buckets.[Market],
Small_Commercial_Buckets.[Parent Co],
Small_Commercial_Buckets.[Policy  Status]
FROM Small_Commercial_Buckets



 WHERE EXISTS ( SELECT 1
				  FROM (SELECT Small_Commercial_Buckets.[Account  Name],Small_Commercial_Buckets.[Account  Name],
							   COUNT(Small_Commercial_Buckets.[Account  Name]) cnt,	
							  
 SUM(IIF([Small_Commercial_Buckets.[Parent Co]) LIKE 'West Bend%',1,0)) Sum_Parent)
						  
FROM (SELECT DISTINCT Small_Commercial_Buckets.[Account  Name],Small_Commercial_Buckets.[Cust  No],Small_Commercial_Buckets.[Parent Co]
								
FROM Small_Commercial_Buckets) AS t2
						
GROUP BY Small_Commercial_Buckets.[Account  Name],Small_Commercial_Buckets.[Cust  No]) AS t3

				WHERE Parent_Sum = 1 AND Cnt = 1
				

AND t1.[Account  Name] = t3.[Account  Name]
				AND t1.[Cust  No] = t3.[Cust  No])
                                            

Open in new window

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39688402
ok. i see that in my query the  field "B.[account name] = A.[Account Name]" has a single space between them,  and your field name have more than one space in between..

"[Account  Name]"

is this a typo or intentionally done ?


try this


SELECT A.[Account  Name],
A.[Cust  No],
A.[Policy No],
A.[Pol Exec],
A.[Pol Rep],
A.[Commission],
A.[Premium],
A.[Market],
A.[Parent Co],
A.[Policy  Status]
FROM Small_Commercial_Buckets AS A
WHERE (((Exists(Select [Account  Name] from Small_Commercial_Buckets AS B where B.[account  name] = A.[Account  Name] and B.[Parent Co] <> 'West Bend Mutual Ins. Co.' ))=False));
0
 

Author Comment

by:Newbi22
ID: 39688414
Capricorn1..... thanks, it works.... The space must have occurred from the original extract out of our management software.

I fixed the upper/lower case and then made sure all the Account Name had a double space in them..

WOW Thanks!!!!!

Wish I could rent you for about 3 months!

Sincerely,
0
 

Author Closing Comment

by:Newbi22
ID: 39688415
This is exactly what I needed. Helps for today and works for tomorrow.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39688428
<Wish I could rent you for about 3 months!> email me. see my profile for address
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39688831
@Newbi22, did you sent me an email? i got an email and could not figure who it came from.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39689082
@Newbi22, i don't understand why you are sending me my ee profile page.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 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

21 Experts available now in Live!

Get 1:1 Help Now