query writing problem.

  • Thread starter Thread starter elijah
  • Start date Start date
E

elijah

i am having a problem writing a query.
table 1 has 2 columns, family & abode. family goes by numbers (1,2 etc).
abode options are apt or house, such as:
family abode
1 house
2 apt
3 house
4 apt
5 apt
table 2 has 2 columns (family, dogcat). family is directly related to family
in table 1. catdog has two options, cat or dog. each family can have more
than 1 cat, such as:
family catdog
1 cat
1 dog
2 cat
3 dog
4 cat
4 cat
5 cat
5 cat
5 dog
i want to be able to query which family's are in apt's & just have cats. i
also want the family number to show only once in the result. the result would
be families 2 & 4. i am having a problem coming up with that result.
thanks to anyone who can help.
 
Hi Elijah,

Perhaps not the most efficient query, but this seems to work...

SELECT Table1.family, Table1.abode, Table2.catdog
FROM Table1 INNER JOIN Table2 ON Table1.family = Table2.Family
WHERE Table1.family In (
SELECT Animals.Family
FROM Table2 As Animals
WHERE catdog="cat"
GROUP BY Animals.Family)
AND Table1.family Not In (
SELECT Animals.Family
FROM Table2 As Animals
WHERE catdog="dog"
GROUP BY Animals.Family)
AND Table1.abode="apt"
GROUP BY Table1.family, Table1.abode, Table2.catdog;


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Back
Top