Solved

Displaying DISTINCT Values using SQL, Access or Pivot table

Posted on 2013-11-22
25
352 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39678568
:-0
0
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

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

Expert Comment

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

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

820 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