Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Displaying DISTINCT Values using SQL, Access or Pivot table

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
Michael Franz
Asked:
Michael Franz
  • 11
  • 9
  • 3
  • +1
1 Solution
 
nutschCommented:
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
 
Michael FranzCFOAuthor Commented:
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
 
Michael FranzCFOAuthor Commented:
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
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.

 
nutschCommented:
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
 
SharathData EngineerCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Michael FranzCFOAuthor Commented:
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
 
nutschCommented:
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
 
Rey Obrero (Capricorn1)Commented:
@Newbi22

the sql statement i posted above at http:#a39670738 will give you the result you want, using an access table.
0
 
Michael FranzCFOAuthor Commented:
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
 
Michael FranzCFOAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
:-0
0
 
Michael FranzCFOAuthor Commented:
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
 
SharathData EngineerCommented:
Did you try my query?
0
 
Rey Obrero (Capricorn1)Commented:
did you try my query? at http:#a39670738
0
 
Michael FranzCFOAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Michael FranzCFOAuthor Commented:
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
 
Michael FranzCFOAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Michael FranzCFOAuthor Commented:
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
 
Michael FranzCFOAuthor Commented:
This is exactly what I needed. Helps for today and works for tomorrow.
0
 
Rey Obrero (Capricorn1)Commented:
<Wish I could rent you for about 3 months!> email me. see my profile for address
0
 
Rey Obrero (Capricorn1)Commented:
@Newbi22, did you sent me an email? i got an email and could not figure who it came from.
0
 
Rey Obrero (Capricorn1)Commented:
@Newbi22, i don't understand why you are sending me my ee profile page.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 9
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now