Displaying DISTINCT Values using SQL, Access or Pivot table

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

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.

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

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') 

Select allOpen in new window

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])

Select allOpen in new window

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));

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")

Select allOpen in new window

and change it to:

<> West Business and <>North Business and  Central Business.

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

@Newbi22

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

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

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.') ;

Select allOpen in new window

:-0

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

Did you try my query?

did you try my query? at http:#a39670738

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

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));


.
.

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.

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])
                                            

Select allOpen in new window

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,

This is exactly what I needed. Helps for today and works for tomorrow.

<Wish I could rent you for about 3 months!> email me. see my profile for address

@Newbi22, did you sent me an email? i got an email and could not figure who it came from.

@Newbi22, i don't understand why you are sending me my ee profile page.