469,356 Members | 2,016 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Opposite of "SELECT DISTINCT"

blyxx86
256 100+
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
12 16847
I think the keyword "COUNT" might be useful in this situation, though I forget the syntax...
Dec 7 '06 #2
http://www.petefreitag.com/item/169.cfm

That says it better than I can
Dec 7 '06 #3
NeoPa
32,185 Expert Mod 16PB
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
256 100+
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
256 100+
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
32,185 Expert Mod 16PB
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
256 100+
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
32,185 Expert Mod 16PB
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
32,185 Expert Mod 16PB
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
3,532 Expert 2GB
In Access Help enter Find Duplicates Query Wizard. I think that's what you want, ready made!
Dec 9 '06 #11
blyxx86
256 100+
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
14,534 Expert Mod 8TB
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.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.