query writing problem.

[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.

 

[Tom Wickerath]

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
__________________________________________