finding all the records in a query with the same first name

[Guest]

I have a table with 5000 records, one of the fields contains first, last name
I want to find all the records that have the same first name. i am trying to
use the wildcards but am not getting anywhere. I want to use the parameter
query. the name will not be the same find all the time.

 

[John Spencer]

Assumption:
The first name is split off by a space. -- John Spencer. Of course names
like Mary Anne Johnson will be treated as if the first name was Mary.

SELECT SomeField
FROM SomeTable
WHERE Left(SomeField, Instr(1,SomeField," ")) in
(SELECT Left(SomeField, Instr(1,SomeField," "))
FROM SomeTable
GROUP BY Left(SomeField, Instr(1,SomeField," "))
HAVING COUNT(*) > 1)

If you can't do this in the SQL window, then you can build a query that
returns all the fields you want. Add a calculated field
Field: MatchThis: Left(SomeField, Instr(1,SomeField," "))

Save the query. And then use the Find Duplicates Query Wizard to have it
show you all the duplicated names.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"lost in a wildcard query" <lost in a wildcard
(e-mail address removed)> wrote in message
news:[email protected]...

 

[John Nurick]

If the field contains data like this

Peter, Cook
Peter, Henderson
Peter, Abbott
Henry, Cook

then you can select all the "Peter" records with this criterion:

LIKE "Peter,*"

In a parameter query, you can achieve this by using something like

LIKE [Enter first name here] & ",*"

 

[Guest]

If the field contains data like this

Peter, Cook
Peter, Henderson
Peter, Abbott
Henry, Cook

then you can select all the "Peter" records with this criterion:

LIKE "Peter,*"

In a parameter query, you can achieve this by using something like

LIKE [Enter first name here] & ",*"

I have a table with 5000 records, one of the fields contains first, last name
I want to find all the records that have the same first name. i am trying to
use the wildcards but am not getting anywhere. I want to use the parameter
query. the name will not be the same find all the time.

that worked great John, now i need to find information using any part of a
field, 750 winding road lane, i need to find this record or any like it
using just the information i know, lets say i know winding.

can this be done using the parameter query?

 

[John Nurick]

Something like this:

LIKE "*" & [Enter any part of the name here] & "*"

If the field contains data like this

Peter, Cook
Peter, Henderson
Peter, Abbott
Henry, Cook

then you can select all the "Peter" records with this criterion:

LIKE "Peter,*"

In a parameter query, you can achieve this by using something like

LIKE [Enter first name here] & ",*"

I have a table with 5000 records, one of the fields contains first, last name
I want to find all the records that have the same first name. i am trying to
use the wildcards but am not getting anywhere. I want to use the parameter
query. the name will not be the same find all the time.

that worked great John, now i need to find information using any part of a
field, 750 winding road lane, i need to find this record or any like it
using just the information i know, lets say i know winding.

can this be done using the parameter query?