By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Opposite of "SELECT DISTINCT"

blyxx86
100+
P: 256
Any idea how to run some SQL that would not Select distinct, but would select the values that are not distinct. Or something like "Select Duplicate"
Dec 7 '06 #1
Share this Question
Share on Google+
12 Replies


P: 33
I think the keyword "COUNT" might be useful in this situation, though I forget the syntax...
Dec 7 '06 #2

P: 33
http://www.petefreitag.com/item/169.cfm

That says it better than I can
Dec 7 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
Blyxx86,

I'm afraid your question is not very clear.
If you can rephrase it in clear terms I'll let you have what you're looking for.

-Adrian.
Dec 8 '06 #4

blyxx86
100+
P: 256
Blyxx86,

I'm afraid your question is not very clear.
If you can rephrase it in clear terms I'll let you have what you're looking for.

-Adrian.
Thank you. I'm trying to select from a table values that are not unique, therefore outputting only values that are duplicates.

I want to be able to compare entries, perhaps a SELECT DUPLICATE command or SELECT SIMILAR, except those are not actual commands.

I just can't figure out how to do it.
Dec 8 '06 #5

blyxx86
100+
P: 256
Thank you. I'm trying to select from a table values that are not unique, therefore outputting only values that are duplicates.

I want to be able to compare entries, perhaps a SELECT DUPLICATE command or SELECT SIMILAR, except those are not actual commands.

I just can't figure out how to do it.
Or perhaps what I am trying to do is similar to this..

Table1 has one field called FirstName with the following entries:
Dave, Joe, Ted, Ted, Frank, Dave, Ted

When the SQL string is set up similar to my "SELECT DUPLICATE" the only values that are shown are:
Ted, Ted, Ted, Dave, Dave (Notice the missing Joe and Frank)

I need these duplicate entries within the table, but need to be able to see them clearly.
Dec 8 '06 #6

NeoPa
Expert Mod 15k+
P: 31,660
Or perhaps what I am trying to do is similar to this..

Table1 has one field called FirstName with the following entries:
Dave, Joe, Ted, Ted, Frank, Dave, Ted

When the SQL string is set up similar to my "SELECT DUPLICATE" the only values that are shown are:
Ted, Ted, Ted, Dave, Dave (Notice the missing Joe and Frank)

I need these duplicate entries within the table, but need to be able to see them clearly.
Sorry Blyxx86.
I thought this thread had an answer :(. I'll look into it now.
It won't be a predicate but we can find some SQL to give the effect you require (might be a little more complicated to understand though).
Dec 8 '06 #7

blyxx86
100+
P: 256
Sorry Blyxx86.
I thought this thread had an answer :(. I'll look into it now.
It won't be a predicate but we can find some SQL to give theeffect you require (might be a little more complicated to understand though).
I think I may have found an answer within this SQL bit...
Expand|Select|Wrap|Line Numbers
  1. SELECT email, 
  2.  COUNT(email) AS NumOccurrences
  3. FROM users
  4. GROUP BY email
  5. HAVING ( COUNT(email) > 1 )
  6.  
Expand|Select|Wrap|Line Numbers
  1. SELECT email
  2. FROM users
  3. GROUP BY email
  4. HAVING ( COUNT(email) = 1 )
  5.  
However, I believe that is straight SQL and will not work work within Access...

So the answer about COUNT was right, but I had no idea how to use that function.

This may help, but I do not know what the COUNT(column) AS NumOccurences reference even means. Well I could guess that it means Count as Number of Occurences, which is kinda what I want, but I want to show each occurence of the field.
Dec 9 '06 #8

NeoPa
Expert Mod 15k+
P: 31,660
Please include the MetaData for all relevant datasets.
Posting Table/Dataset MetaData
Here is an example of how to post table MetaData :
Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric
Without that, I will try to illustrate how to do this generally.
Assume we are dealing with the table tblStudent shown above.
To produce a list of all student names which are not unique :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tblStudent INNER JOIN (SELECT [Name]
  3. FROM tblStudent
  4. GROUP BY [Name]
  5. HAVING Count([Name]) > 1) AS subDuplicates
  6.     ON tblStudent.Name = subDuplicates.Name
The subquery (defined within the () after INNER JOIN) will produce the actual list of names which are not unique, but will only display one instance of the name.
Dec 9 '06 #9

NeoPa
Expert Mod 15k+
P: 31,660
I think I may have found an answer within this SQL bit...
Expand|Select|Wrap|Line Numbers
  1. SELECT email, 
  2.  COUNT(email) AS NumOccurrences
  3. FROM users
  4. GROUP BY email
  5. HAVING ( COUNT(email) > 1 )
  6.  
Expand|Select|Wrap|Line Numbers
  1. SELECT email
  2. FROM users
  3. GROUP BY email
  4. HAVING ( COUNT(email) = 1 )
  5.  
However, I believe that is straight SQL and will not work work within Access...

So the answer about COUNT was right, but I had no idea how to use that function.

This may help, but I do not know what the COUNT(column) AS NumOccurences reference even means. Well I could guess that it means Count as Number of Occurences, which is kinda what I want, but I want to show each occurence of the field.
When you have AS after something this means that the following text (name) will be considered the name of this item for future reference. In your case, NumOccurences will be the name that indicates COUNT(column).
You could also say something like
Expand|Select|Wrap|Line Numbers
  1. SELECT u.*
  2. FROM Users AS u
  3. ORDER BY u.Name
Every u. would be equivalent to Users..
Dec 9 '06 #10

missinglinq
Expert 2.5K+
P: 3,532
In Access Help enter Find Duplicates Query Wizard. I think that's what you want, ready made!
Dec 9 '06 #11

blyxx86
100+
P: 256
I modified the code just a bit to fit one of my tables.

Expand|Select|Wrap|Line Numbers
  1. SELECT QC.Serial, 
  2. Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date], 
  3. Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
  4. FROM QC
  5. GROUP BY QC.Serial
  6. HAVING ((Count(QC.Serial))>1);
  7.  
If I try to add another field to the SQL there, it gives me an error.
You tried to execute a query that does not include the specified expression 'Tech' as part of an aggregate function.
Expand|Select|Wrap|Line Numbers
  1. SELECT QC.Tech, 
  2. QC.Serial, 
  3. Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date],
  4.  Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
  5. FROM QC
  6. GROUP BY QC.Serial
  7. HAVING (((Count(QC.Serial))>1));
  8.  
I read up about how it isn't part of a TRANSFORM query, and how doing this would essentially create a cross-tab query.. The equation just gets more and more complex... I feel comfortable with VBA and Access one day, and the next it poses a new challenge. How... fun!
Dec 15 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
I modified the code just a bit to fit one of my tables.

Expand|Select|Wrap|Line Numbers
  1. SELECT QC.Serial, 
  2. Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date], 
  3. Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
  4. FROM QC
  5. GROUP BY QC.Serial
  6. HAVING ((Count(QC.Serial))>1);
  7.  
If I try to add another field to the SQL there, it gives me an error.
You tried to execute a query that does not include the specified expression 'Tech' as part of an aggregate function.
Expand|Select|Wrap|Line Numbers
  1. SELECT QC.Tech, 
  2. QC.Serial, 
  3. Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date],
  4.  Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
  5. FROM QC
  6. GROUP BY QC.Serial
  7. HAVING (((Count(QC.Serial))>1));
  8.  
I read up about how it isn't part of a TRANSFORM query, and how doing this would essentially create a cross-tab query.. The equation just gets more and more complex... I feel comfortable with VBA and Access one day, and the next it poses a new challenge. How... fun!
If you add a field you will have to include it in the Group By

Expand|Select|Wrap|Line Numbers
  1. SELECT QC.Tech, QC.Serial, 
  2. Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date],
  3.  Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
  4. FROM QC
  5. GROUP BY QC.Tech, QC.Serial
  6. HAVING (((Count(QC.Serial))>1));
  7.  
Mary
Dec 15 '06 #13

Post your reply

Sign in to post your reply or Sign up for a free account.