Opposite of "SELECT DISTINCT"

[blyxx86]

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"

[Sandro997]

I think the keyword "COUNT" might be useful in this situation, though I forget the syntax...

[Sandro997]

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

That says it better than I can

[NeoPa]

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.

[blyxx86]

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.

[blyxx86]

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.

[NeoPa]

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

[blyxx86]

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...

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
 

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
 

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.

[NeoPa]

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 :

SELECT *
FROM tblStudent INNER JOIN (SELECT [Name]
FROM tblStudent
GROUP BY [Name]
HAVING Count([Name]) > 1) AS subDuplicates
    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.

[NeoPa]

I think I may have found an answer within this SQL bit...

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
 

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
 

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

SELECT u.*
FROM Users AS u
ORDER BY u.Name

Every u. would be equivalent to Users..

[missinglinq]

In Access Help enter Find Duplicates Query Wizard. I think that's what you want, ready made!

[blyxx86]

I modified the code just a bit to fit one of my tables.

SELECT QC.Serial, 
Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date], 
Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
FROM QC
GROUP BY QC.Serial
HAVING ((Count(QC.Serial))>1);
 

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.

SELECT QC.Tech, 
QC.Serial, 
Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date],
 Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
FROM QC
GROUP BY QC.Serial
HAVING (((Count(QC.Serial))>1));
 

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!

[MMcCarthy]

I modified the code just a bit to fit one of my tables.

SELECT QC.Serial, 
Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date], 
Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
FROM QC
GROUP BY QC.Serial
HAVING ((Count(QC.Serial))>1);
 

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.

SELECT QC.Tech, 
QC.Serial, 
Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date],
 Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
FROM QC
GROUP BY QC.Serial
HAVING (((Count(QC.Serial))>1));
 

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

SELECT QC.Tech, QC.Serial, 
Max(IIf([Overall]='Pass',[Date],Null)) AS [Pass Date],
 Max(IIf([Overall]='Fail',[Date],Null)) AS [Fail Date]
FROM QC
GROUP BY QC.Tech, QC.Serial
HAVING (((Count(QC.Serial))>1));
 

Mary