Query help

Hello,

I have imported a databae into access. Essentially I have
a database of names and addresses. all of the names in
the databae have multiple records with the same names but
different addresses. What I am trying to do is get a
report of records based on how many times there name
appears in the database. For example I want a report for
anyone whos name appears in the database 3 or more times
or 5 or more times etc.

so i might have a set of records like this

Joe A 123 main street
Joe A 123 main street
Joe A 123 main street
joe A 123 main street
joe B 555 west street
Joe C 142 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe D 222 south lane
Joe D 222 south lane

I want to get a list of anyone with more than 3 records
in the database.
Hope this is clear and thank you for any help
S

The following returns all names that appear in more than 3
rows in a table. You should be able to amend it to achieve
your result.

SELECT nameColumn
FROM MyTable
GROUP BY nameColumn
HAVING Count(1) 3

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hello,

I have imported a databae into access. Essentially I have
a database of names and addresses. all of the names in
the databae have multiple records with the same names but
different addresses. What I am trying to do is get a
report of records based on how many times there name
appears in the database. For example I want a report for
anyone whos name appears in the database 3 or more times
or 5 or more times etc.

so i might have a set of records like this

Joe A 123 main street
Joe A 123 main street
Joe A 123 main street
joe A 123 main street
joe B 555 west street
Joe C 142 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe D 222 south lane
Joe D 222 south lane

I want to get a list of anyone with more than 3 records
in the database.
Hope this is clear and thank you for any help
S
.

Appreciate the quick reply and help!!!
It looks like what I want to do, but I have am elementary
question..... Where do I put this information? I tried
putting it on the criteria line (inserted my real field
name and table name) in the query design view but it does
not work. Obviously I am a novice with access. I
appreciate your help .
-----Original Message-----
The following returns all names that appear in more than
3
rows in a table. You should be able to amend it to
achieve
your result.

SELECT nameColumn
FROM MyTable
GROUP BY nameColumn
HAVING Count(1) 3

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hello,

I have imported a databae into access. Essentially I
have
a database of names and addresses. all of the names in
the databae have multiple records with the same names
but
different addresses. What I am trying to do is get a
report of records based on how many times there name
appears in the database. For example I want a report
for
anyone whos name appears in the database 3 or more
times
or 5 or more times etc.

so i might have a set of records like this

Joe A 123 main street
Joe A 123 main street
Joe A 123 main street
joe A 123 main street
joe B 555 west street
Joe C 142 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe D 222 south lane
Joe D 222 south lane

I want to get a list of anyone with more than 3 records
in the database.
Hope this is clear and thank you for any help
S
.

.

Do a Totals query (View, Totals from Query Design view).
Select the Name field (or the PK that refers to the name)
and any other field. Group By the name, and enter Count
for the other.

Add the criteria =3 to the field being counted.

HTH
Kevin Sprinkel


-----Original Message-----
Hello,

I have imported a databae into access. Essentially I have
a database of names and addresses. all of the names in
the databae have multiple records with the same names but
different addresses. What I am trying to do is get a
report of records based on how many times there name
appears in the database. For example I want a report for
anyone whos name appears in the database 3 or more times
or 5 or more times etc.

so i might have a set of records like this

Joe A 123 main street
Joe A 123 main street
Joe A 123 main street
joe A 123 main street
joe B 555 west street
Joe C 142 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe D 222 south lane
Joe D 222 south lane

I want to get a list of anyone with more than 3 records
in the database.
Hope this is clear and thank you for any help
S
.

I wrote too soon. I figured it out on my own . PLease
disregard. Thanks again for your help
S
-----Original Message-----
Hello,

I have imported a databae into access. Essentially I
have
a database of names and addresses. all of the names in
the databae have multiple records with the same names
but
different addresses. What I am trying to do is get a
report of records based on how many times there name
appears in the database. For example I want a report for
anyone whos name appears in the database 3 or more times
or 5 or more times etc.

so i might have a set of records like this

Joe A 123 main street
Joe A 123 main street
Joe A 123 main street
joe A 123 main street
joe B 555 west street
Joe C 142 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe D 222 south lane
Joe D 222 south lane

I want to get a list of anyone with more than 3 records
in the database.
Hope this is clear and thank you for any help
S
.

I would probably get the sequence wrong if I tried to
explain it in the Design View. From the query design view,
select View-SQL View to see the underlying SQL, make the
changes to my SQL to suit your database, then switch back
to Design View to see how it looks.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Appreciate the quick reply and help!!!
It looks like what I want to do, but I have am elementary
question..... Where do I put this information? I tried
putting it on the criteria line (inserted my real field
name and table name) in the query design view but it does
not work. Obviously I am a novice with access. I
appreciate your help .
-----Original Message-----
The following returns all names that appear in more than
3
rows in a table. You should be able to amend it to
achieve
your result.

SELECT nameColumn
FROM MyTable
GROUP BY nameColumn
HAVING Count(1) 3

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hello,

I have imported a databae into access. Essentially I
have
a database of names and addresses. all of the names in
the databae have multiple records with the same names
but
different addresses. What I am trying to do is get a
report of records based on how many times there name
appears in the database. For example I want a report
for
anyone whos name appears in the database 3 or more
times
or 5 or more times etc.

so i might have a set of records like this

Joe A 123 main street
Joe A 123 main street
Joe A 123 main street
joe A 123 main street
joe B 555 west street
Joe C 142 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe C 123 north street
Joe D 222 south lane
Joe D 222 south lane

I want to get a list of anyone with more than 3 records
in the database.
Hope this is clear and thank you for any help
S
.

.

.