Extract records with a specific field appearing more than once in the DB

[markx]

Greetings everybody,

I'm quite new to Access and SQL - until now worked a lot on Excel and some
VBA macros...

I would like to write a query that will extract me all the records where one
particular field (f. ex. family name) appears more than once through the
database.

Exemple:
Name First Name Address Age
Johnson Anne 47th Ave 40
Hill John 5th Ave 57
Johnson Adrian Wall Street 33
.... would like to extract the first and the third record (because the "name"
field in these records (=Johnson) apprears more than once in the database)

I'm also wondering what is the best way in Access to extract records based
on their frequency (in one particular field), f. ex. extract records with
the unique specific field, then extract all the records with the specific
field appearing twice in the whole database, then three times and so on...
Suppose it should be someting easy, but don't know where to find an answer.
Have also some problem with the MS Access SQL synthax.

Thanks for your help on this,
Mark

 

[Dennis]

If you click on the new query button, you will see that there is a wizard for
finding duplicates.

 

[Michel Walsh]

Hi,


Bring the table in the designer. Click the Summation button (the capital
Sigma, a rotated M by 90 degree) to get an additional line in the grid. Drag
field Name in the grid, keep the proposed GROUP BY. Drag it again, a second
time, this time, change the GROUP BY to COUNT. Under this, in the criteria,
type >1. You should then get the family name where there count (number of
time they appear) is > 1.


Hoping it may help,
Vanderghast, Access MVP

 

[Chaim]

Following Michel's lead, the second part of your question would be answered
by setting the Sort to Ascending. Michel's query will give you the counts
(assuming you have a checkmark in the Show box), and you can sort on the
count column.

Good Luck!

 

[markx]

Thanks guys for your precious help!
It worked exactly as you explained.

May I also have another, accessory, question regarding the example from the
previous post?
In fact, with your method (at least as applied by myself) I just receive
someting like:
Name Count of Name
Johnson 2
Morgan 5
Kimberley 6

How could I elaborate further on this if I would like to just extract the
double (and more) records and receive something like this:

Name First Name Address Age
Johnson Anne 47th Ave 40
Johnson Adrian Wall Street 33
(the record concerning "Hill John" will not appear because his family name
appears only once in the database)

Furthermore, how could I extract all the records where AT THE SAME TIME
"Name" and "First Name" appear together more than once (if "Johnson Anne"
appears twice, her record will be extracted).

Thanks again for your comments!
Mark

 

[Michel Walsh]

Hi,


Save that query, say, under the name Q1.

Make another query, bring Q1 and your original table, make a join between
them, through their common field [Name]. Drag the desired fields in the
grid.

That is not more complex than that.



Hoping it may help,
Vanderghast, Access MVP

 

[markx]

Thanks!
Didn't think about it! But does it also mean that there is no direct SQL
query that will do the job without multiplying the quantity of the queries?

And concerning the second question, regarding "concatenation" - could any of
you give me some hint how to imagine the query that will extract the
duplicate records, but based on two fields ("Name" and "First Name"
together)? It's not that I'm soooo lazy that I don't want to find it out by
myself, but I'm just afraid that for the time being I don't have enough
basic knowledge of MS Access/SQL to bring the solution to the table without
your support.

Looking forward to your comments,
Mark

Hi,


Save that query, say, under the name Q1.

Make another query, bring Q1 and your original table, make a join between
them, through their common field [Name]. Drag the desired fields in the
grid.

That is not more complex than that.



Hoping it may help,
Vanderghast, Access MVP

Thanks guys for your precious help!
It worked exactly as you explained.

May I also have another, accessory, question regarding the example from
the previous post?
In fact, with your method (at least as applied by myself) I just
receive someting like:
Name Count of Name
Johnson 2
Morgan 5
Kimberley 6

How could I elaborate further on this if I would like to just extract the
double (and more) records and receive something like this:

Name First Name Address Age
Johnson Anne 47th Ave 40
Johnson Adrian Wall Street 33
(the record concerning "Hill John" will not appear because his family
name appears only once in the database)

Furthermore, how could I extract all the records where AT THE SAME TIME
"Name" and "First Name" appear together more than once (if "Johnson Anne"
appears twice, her record will be extracted).

Thanks again for your comments!
Mark

 

[Michel Walsh]

Hi,


drag the second field in the first query, keep the proposed GROUP BY.


In the second query, join now on both fields.


You can do in one query, using "sub query". Basically, here, it is
equivalent, but easier to maintain, doing it in two queries, no? You just
cannot do it GRAPHICALLY in one query, but in SQL view, you could.



Hoping it may help,
Vanderghast, Access MVP

Thanks!
Didn't think about it! But does it also mean that there is no direct SQL
query that will do the job without multiplying the quantity of the
queries?

And concerning the second question, regarding "concatenation" - could any
of you give me some hint how to imagine the query that will extract the
duplicate records, but based on two fields ("Name" and "First Name"
together)? It's not that I'm soooo lazy that I don't want to find it out
by myself, but I'm just afraid that for the time being I don't have
enough basic knowledge of MS Access/SQL to bring the solution to the table
without your support.

Looking forward to your comments,
Mark

Hi,


Save that query, say, under the name Q1.

Make another query, bring Q1 and your original table, make a join between
them, through their common field [Name]. Drag the desired fields in the
grid.

That is not more complex than that.



Hoping it may help,
Vanderghast, Access MVP

Thanks guys for your precious help!
It worked exactly as you explained.

May I also have another, accessory, question regarding the example from
the previous post?
In fact, with your method (at least as applied by myself) I just
receive someting like:
Name Count of Name
Johnson 2
Morgan 5
Kimberley 6

How could I elaborate further on this if I would like to just extract
the double (and more) records and receive something like this:

Name First Name Address Age
Johnson Anne 47th Ave 40
Johnson Adrian Wall Street 33
(the record concerning "Hill John" will not appear because his family
name appears only once in the database)

Furthermore, how could I extract all the records where AT THE SAME TIME
"Name" and "First Name" appear together more than once (if "Johnson
Anne" appears twice, her record will be extracted).

Thanks again for your comments!
Mark




Following Michel's lead, the second part of your question would be
answered
by setting the Sort to Ascending. Michel's query will give you the
counts
(assuming you have a checkmark in the Show box), and you can sort on
the
count column.

Good Luck!
--

Chaim


Hi,


Bring the table in the designer. Click the Summation button (the
capital
Sigma, a rotated M by 90 degree) to get an additional line in the
grid.
Drag
field Name in the grid, keep the proposed GROUP BY. Drag it again, a
second
time, this time, change the GROUP BY to COUNT. Under this, in the
criteria,
type >1. You should then get the family name where there count (number
of
time they appear) is > 1.


Hoping it may help,
Vanderghast, Access MVP


Greetings everybody,

I'm quite new to Access and SQL - until now worked a lot on Excel
and
some
VBA macros...

I would like to write a query that will extract me all the records
where
one particular field (f. ex. family name) appears more than once
through
the database.

Exemple:
Name First Name Address Age
Johnson Anne 47th Ave 40
Hill John 5th Ave 57
Johnson Adrian Wall Street 33
... would like to extract the first and the third record (because
the
"name" field in these records (=Johnson) apprears more than once in
the
database)

I'm also wondering what is the best way in Access to extract records
based
on their frequency (in one particular field), f. ex. extract records
with
the unique specific field, then extract all the records with the
specific
field appearing twice in the whole database, then three times and so
on...
Suppose it should be someting easy, but don't know where to find an
answer. Have also some problem with the MS Access SQL synthax.

Thanks for your help on this,
Mark