Using Fields as Multiple Search Values in a Query (1 Viewer)

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 field that the query should search for. Let me give a simplified example:

The table of values:
TableName | FieldName
-----------------------
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 count the number of instances of 'Apples', as would be the case for each row.

How do I do this? And how do I write the query so that the table values will essentially act as variables? Do I need to use VBA?

I'm a bit of an Access noob, so any help would be appreciated - especially since I've been doing this manually. It's taking a lifetime.

You really should not design your table(s) this way. You are doing the work of the smart people who designed and built the DBMS.

FoodStuff
FoodCategory|FoodName
Fruits | Apples
Fruits | Bananas
Fruits | Oranges
Vegetables | Mushrooms
Vegetables | Carrots

You can further remove the potential for data entry anomalies (errors) by looking up the category in another table and using a reference from that table. If you need to know what category of food an Apple is, you would simply query your table for FoodName = "Apples". If you need to know all the Vegetables, you would simply query your table for FoodCategory = "Vegetables" (or do it in the joined table).

Thanks for the quick reply George!

Just an FYI, I had no say in the design of the database or tables. I'm just trying to extract some information about the type and quantity of information that is contained in certain fields.

I don't need to look up whether 'Apple' pertains to 'Fruits' or 'Vegetables." I have all that information. In fact, I know that I want to query only the 'Fruits' table for 'Apples.' That's what I'm really trying to get at.

Perhaps I should be more explicit in my explanation. Let's take an example query:

SELECT Count (*)
FROM %FoodCategory%
WHERE %FoodName% IS NOT NULL;

Now, I know the %FoodCategory% and %FoodName% "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!