Duplicates

Stephen Preston

Hi

I wish to perform a search on a column of a table. The column contains
surnames. I wish the results to return the entries which contain more than
one instance of a surname. eg.

ID__Surname
1___Jones
2___Smith
3___Jones
4___Carter
5___Jones
6___Wilson
7___Smith

The result I'm after is

1___Jones
3___Jones
5___Jones
2___Smith
7___Smith

Is this possible with one query? and if so what?

Thanks

Stephen

Aug 8 '06 #1

Subscribe Post Reply

2080

strawberry

Stephen Preston wrote:

Hi

I wish to perform a search on a column of a table. The column contains
surnames. I wish the results to return the entries which contain more than
one instance of a surname. eg.

ID__Surname
1___Jones
2___Smith
3___Jones
4___Carter
5___Jones
6___Wilson
7___Smith

The result I'm after is

1___Jones
3___Jones
5___Jones
2___Smith
7___Smith

Is this possible with one query? and if so what?

Thanks

Stephen

Can you use subqueries? If so, this would work:

SELECT t1.id, t1.lastname
FROM (

SELECT DISTINCT lastname, count( lastname )
FROM people
GROUP BY lastname
HAVING count( lastname ) >1
)t2
LEFT JOIN people t1 ON t1.lastname = t2.lastname

Aug 8 '06 #2

by: Michelle | last post by:

hi, i have created an array from recordset containing user names eg. (davidp, davidp, evenf, patricka, rebeccah) which i have sorted in alphabetical order, but i need to identify duplicates...

ASP / Active Server Pages

Eliminating Duplicates in MS Access while updating them

by: Marlene | last post by:

Hi All I have the following scenario, where I have found all the duplicates in a table, based on an order number and a part number (item).I might have something like this: Order PODate Rec...

Microsoft Access / VBA

Removing duplicates from query, but not from table

by: tyrfboard | last post by:

I've been searching for awhile now on how to remove duplicates from a table within an Access db and have found plenty of articles on finding or deleting duplicates. All I want to do is remove them...

Microsoft Access / VBA

Resolving duplicates entries in table among 10 databases

by: AK | last post by:

Hi Our product uses MS-SQL Server 2000. One of our customer has 10 installations with each installation stroring data in its own database. Now the customer wants to consolidate these databases...

Microsoft SQL Server

removing duplicates from container,

by: vsgdp | last post by:

I have a container of pointers. It is possible for two pointers to point to the same element. I want to remove duplicates. I am open to which container is best for this. I thought of using...

C / C++

duplicates in array

by: ak | last post by:

Is it possible to find repeated(duplicate) element in an array in single loop ? AK

C / C++

Index based on 2 fields (No Duplicates)

by: ryan.paquette | last post by:

In the table there are 2 fields in which I wish to limit (i.e. No Duplicates) Although I do not want to limit them to "No Duplicates" separately. I need them to be limited to "No Duplicates" as...

Microsoft Access / VBA

Checking for Duplicates in Multiple Fields beforeupdate

by: john.cole | last post by:

I have searched all the groups I can, and I still haven't been able to come up the solution I need. I have the following problem. In my form named sbfrmSpoolList, I am entering a job, spool and...

Microsoft Access / VBA

How to check Internal and External duplicates?

by: tskmjk55 | last post by:

Recently, I have a requirement to develop a vb.net application wherein the input excel sheet data which has an average of 5000 records should be checked for Internal duplicates (duplicates within the...

Visual Basic .NET

Finding duplicates in an array

by: Thekid | last post by:

I'm trying to figure out a way to find if there are duplicates in an array. My idea was to take the array as 'a' and make a second array as 'b' and remove the duplicates from 'b' using 'set' and then...

Python

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

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

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

The easy way to turn off automatic updates for Windows 10/11

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

Windows Server

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

Similar topics