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"
12  17531 
I think the keyword "COUNT" might be useful in this situation, though I forget the syntax...
NeoPa 32,556
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.
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.
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 32,556
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).
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 32,556
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 : - 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 32,556
Expert Mod 16PB
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..
In Access Help enter Find Duplicates Query Wizard. I think that's what you want, ready made!
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!
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Phil Powell |
last post by:
Has anyone here ever done a case where you have a select multiple form
element and you have to do both server-side and client-side validation?
I am honestly not sure how to do it in Javascript (I...
|
by: Matt |
last post by:
In ASP page, there is a "SELECT ALL" button, when user click it, it will
select all checkboxes. I am not sure should I use client-side code to do
that? the following is my approach but it didnt...
|
by: Steve |
last post by:
Hi, Is there something fundamentally wrong with the following query?
SELECT MAX(OrderID) AS Expr1 FROM Orders WHERE (MarketActionCode = 'S') AND
(OrderLegCode = 'S') AND (Status = 'F') AND...
|
by: GSteven |
last post by:
(as formerly posted to microsoft.public.access.forms with no result)
I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is...
|
by: Dave Markle |
last post by:
Good afternoon. I was just going through my code, analyzing it with FXCop,
and FxCop gave me the following error on this code:
MY CODE:
Select Case termYears
Case 5 : retVal.Append("1")
Case...
|
by: ALaurie10 |
last post by:
I am trying to write a "Select and From Statement but keep getting error 214727900 and my VBA points to the .Open Options:=acCmdTable. I am referencing a table in my Select. Can someone help me out....
|
by: bcurtu |
last post by:
Hi,
I have a BIIIIIG problem with the next query:
cursor.execute("""
SELECT titem.object_id, titem.tag_id
FROM tagging_taggeditem titem
WHERE titem.object_id IN (%s)
""",( eid_list))
|
by: bre1603 |
last post by:
I have a continuous form in Access 2007 called “Leadership Contact List.” It has a checkbox control for each record (bound to a field in the underlying table) that is used to email or create mailing...
|
by: Utku |
last post by:
Is it possible to add "select all" and "select none" buttons to MultiChoiceDialog in wxPython?
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
