select countries appearing in at least 3 of 5 tables

[tchristo]

Hi folks,

I would like to select countries that appear in at least 3 of 5 tables. These tables show the bottom 25 countries for different statistics (e.g. %poverty,%malnourished, etc.).
The purpose is to find "the worst of the worst" countries.
I can easily make a query that shows only those countries that appear in all of the 5 tables. But I can't figure out how to do this slightly-less-selective a query.

Thanks for any insights.
-tregc

 

[PHV]

Create a saved union query named, say, qryListCountries:
SELECT Country, 'Table1' AS TableName FROM Table1
UNION SELECT Country, 'Table2' FROM Table2
...
UNION SELECT Country, 'Table5' FROM Table5;

And now your worst of the worst query:
SELECT Country, Count(*) AS CountOfTable
FROM qryListCountries
GROUP BY Country
HAVING Count(*)>=3;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tchristo]

Thanks, I've never used a union query before...that worked great!