Table Values As Query Input?

[joolio]

I've been looking for a solution for this problem everywhere but to no avail. I have a table with a list of values that I need to use as search criteria for a query. More specifically, this table contains two columns of values: 1) the name of the table where the query should search and 2) the column that the query should search. Let me give a simplified example:

The table of values:
TableName | ColumnName
-----------------------
Fruits | Apples
Fruits | Bananas
Fruits | Oranges
Vegetables | Mushrooms
Vegetables | Carrots


So the list would be passed five times through the query to produce 5 different results. For instance, the query would be pointed to the 'Fruits' table to sort through the information in the 'Apples' column, as would be the case for each row.

Let's take an example query:

SELECT Count (*)
FROM %TableName%


WHERE %ColumnName% IS NOT NULL;

Now, I know the %TableName% and %ColumnName% "variables" are not correct, but I just wanted them to serve as placeholders. What I would like to happen is have each variable replaced with a corresponding set of values from my table. So it would look like:

SELECT Count (*)
FROM Fruits
WHERE Apples IS NOT NULL;

SELECT Count (*)
FROM Fruits
WHERE Bananas IS NOT NULL;

SELECT Count (*)
FROM Fruits
WHERE Oranges IS NOT NULL;

SELECT Count (*)
FROM Vegetables
WHERE Mushrooms IS NOT NULL;

SELECT Count (*)
FROM Vegetables
WHERE Carrots IS NOT NULL;

Thus every row would be treated as a separate query, and this would be done automatically. I have NO idea how to do this, as I'm doing it manually now. Thank you for your help!

[RuralGuy]

In the example you supplied I believe Apples should be a record in the Fruits table and not a field. Same with the Vegetable table. It looks to me as though you may have a normalization issue first.

[alcoool]

try this as a criteria
In (select[field name]from[table name])