?
Solved

Help With SQL

Posted on 2007-10-18
6
Medium Priority
?
157 Views
Last Modified: 2010-03-20
Hi,

I thought this would be a simple problem to solve, but I am not getting anywhere. I have a table called families, that has the name, address, city, state, zip, and id code. What I would like to do is print out all the families names, addresses etc but only if the number of members in the family is 4 or greater. Any help would be greatly appreciated.
0
Comment
Question by:Mark1110
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20102891
Is the number of members also in that table, or does that come from a different table?

Jim
0
 

Author Comment

by:Mark1110
ID: 20103135
There is no number in the table, just the name. address, etc. Is it possible to do it in one SQL statement?

Mark
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20103185
Mark - Now I'm confused.
"but only if the number of members in the family is 4 or greater"

What in the database tells us how many family members exist for any given family?

Jim
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20103282
select count(name) namecnt, name, address, city, state, zip
from families
group by  name, address, city, state, zip
having count(name)>=4
0
 

Author Comment

by:Mark1110
ID: 20104563
It is an access database. each record has a name, address, city, state, zip, and id_code which identifies which record is for the same family. For example John Smith has a wife and three kids, they would all have the same id_code of "100001".

Hope that clarifies it.

Mark
0
 
LVL 22

Accepted Solution

by:
JimBrandley earned 500 total points
ID: 20104589
You can use the seletc presented by SQL_SERVER_DBA with a few changes:
select count(id_code) namecnt, name, address, city, state, zip
from families
group by  name, address, city, state, zip
having count(id_code)>=4

Jim
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question