Establishing Limits

N

nikik

New Member
Joined
Apr 7, 2011
Messages
21
I would like Access to help me make a series of menus. I have one table listing all of my food items. I need to find a query that will return all acceptable combinations of two things:

1. Each meal must have a protein, grain, veg, fruit & milk
2. Each meal must be between 550-560 calories

I cannot have a different table for protein, grain, veg, fruit & milk because some of the items are made up for more than one meal component.

Can anyone help me with this plan? I've only done very simple things in Access.

Thank you! :)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
So you have a table that has three columns: FoodName, FoodType, Calories. This is probably not the best way to do this but a brute force strategy comes to mind.

Include in your table a food named "None" for each type with Calories = 0

Write a query for each type of food. Name them something like qryProtein, qryGrain, etc.

Write another query which includes each of these queries. Make sure there are no links between them in the QBE grid. This is called a Cartesian join and will return all possible combinations. This could potentially be a very large recordset. For fields include the FoodName, FoodType and Calories from each query. Create a calculated column for totalling the calories "TotalCalories: qryProtein.Calories + qryGrain.Calories + qryVegetable.Calories + qryFruit.Calories + qryMilk.Calories"

Then you'll write another query that returns only those meals you want between TotalCalories >=550 and <=650.

Or something like that. This is off the top of my head, and as I said, could probably be done in a niftier way.

hth,

Rich
 
Upvote 0
I get an error saying "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and a query based on that table. If so, try choosing fields from only the table or only the query."

I didn't make any relationships, as it's only one table. Help??

Thanks!
 
Upvote 0
Hmm, I haven't used the query wizard for some time. I'd guess you need to check the spelling of your tables and fields. Also if you have spaces and weird characters I'd recommend not doing that.

Otherwise, I created a simple table named tblFoods (the numbers are for example, I have no idea what calories would be appropriate).

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblFoods</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>FoodID</TH><TH bgColor=#c0c0c0 borderColor=#000000>FoodName</TH><TH bgColor=#c0c0c0 borderColor=#000000>FoodType</TH><TH bgColor=#c0c0c0 borderColor=#000000>Calories</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0>None</TD><TD borderColor=#c0c0c0>Protein</TD><TD borderColor=#c0c0c0 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0>None</TD><TD borderColor=#c0c0c0>Grain</TD><TD borderColor=#c0c0c0 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0>None</TD><TD borderColor=#c0c0c0>Vegetable</TD><TD borderColor=#c0c0c0 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0>None</TD><TD borderColor=#c0c0c0>Fruit</TD><TD borderColor=#c0c0c0 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0>None</TD><TD borderColor=#c0c0c0>Milk</TD><TD borderColor=#c0c0c0 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0>Pork</TD><TD borderColor=#c0c0c0>Protein</TD><TD borderColor=#c0c0c0 align=right>10</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>7</TD><TD borderColor=#c0c0c0>Bread</TD><TD borderColor=#c0c0c0>Grain</TD><TD borderColor=#c0c0c0 align=right>20</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>8</TD><TD borderColor=#c0c0c0>Carrot</TD><TD borderColor=#c0c0c0>Vegetable</TD><TD borderColor=#c0c0c0 align=right>30</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>9</TD><TD borderColor=#c0c0c0>Apple</TD><TD borderColor=#c0c0c0>Fruit</TD><TD borderColor=#c0c0c0 align=right>40</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0>Milk</TD><TD borderColor=#c0c0c0>Milk</TD><TD borderColor=#c0c0c0 align=right>50</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>11</TD><TD borderColor=#c0c0c0>Beef</TD><TD borderColor=#c0c0c0>Protein</TD><TD borderColor=#c0c0c0 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>12</TD><TD borderColor=#c0c0c0>Cereal</TD><TD borderColor=#c0c0c0>Grain</TD><TD borderColor=#c0c0c0 align=right>10</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>13</TD><TD borderColor=#c0c0c0>Artichoke</TD><TD borderColor=#c0c0c0>Vegetable</TD><TD borderColor=#c0c0c0 align=right>15</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>14</TD><TD borderColor=#c0c0c0>Grapes</TD><TD borderColor=#c0c0c0>Fruit</TD><TD borderColor=#c0c0c0 align=right>20</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>15</TD><TD borderColor=#c0c0c0>Cheese</TD><TD borderColor=#c0c0c0>Milk</TD><TD borderColor=#c0c0c0 align=right>25</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I also created five queries like this, each with a different FoodType named qryFruit, qryMilk, etc

Code: 
SELECT tblFoods.*
FROM tblFoods
WHERE (((tblFoods.FoodType)="Fruit"));

Then this query returns combinations of foods within the set parameters.

Code: 
SELECT qryFruit.FoodName, qryFruit.FoodType, qryFruit.Calories, qryGrain.FoodName, qryGrain.FoodType, qryGrain.Calories, qryMilk.FoodName, qryMilk.FoodType, qryMilk.Calories, qryProtein.FoodName, qryProtein.FoodType, qryProtein.Calories, qryVegetable.FoodName, qryVegetable.FoodType, qryVegetable.Calories, [qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories] AS TotalCalories
FROM qryFruit, qryGrain, qryMilk, qryProtein, qryVegetable
WHERE ((([qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories])>110 And ([qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories])<140));

If you are still having problems with the wizard, just create the queries from scratch. Open a fresh, new query and go in to SQL view. Paste the above SQL and change as appropriate.

hth,

Rich
 
Upvote 0
Wow - thanks! That really works! Now I can add the nutrient info w/ limits, which will help me understand why it's doing what it's doing. Thanks SOOO much - have a great weekend!! :biggrin:
 
Upvote 0
I've been messing with this query and think it would work better for what I'm doing if the query recognized their needed to "1" serving of each catagory. I have the food items listed by servings, such as: Pizza....Bread 2, Protein 1, Veg 1/8. It would be perfect if the query would tally up items in those catagories to make a full meal of 1 Bread, 1 Protein, 1 Veg, 1 Fruit & 1 Milk.

Thanks in advance for any help!
 
Upvote 0
I think the best way to describe it is I need it to do a filter, but return all the data that fits.

I can do a manual filter with a total line and eyeball it to make sure it has all the meal components, calories, vit A, vit C, sodium limits, etc, but there has to be a way for access to do an accross the board filter based on the limits I set, right?

Thanks!
 
Upvote 0
Filter/Query?

(I thought it would be easier to start a new thread)

I have this starter table:

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Table 2</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>FoodName</TH><TH borderColor=#000000 bgColor=#c0c0c0>Description</TH><TH borderColor=#000000 bgColor=#c0c0c0>Brand</TH><TH borderColor=#000000 bgColor=#c0c0c0>Brand ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Vendor ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>Srvg Size</TH><TH borderColor=#000000 bgColor=#c0c0c0>Cost/Srvg</TH><TH borderColor=#000000 bgColor=#c0c0c0>Calories</TH><TH borderColor=#000000 bgColor=#c0c0c0>Protein (g)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Calcium (mg)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Iron (mg)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Vit A (IU)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Vit C(mg)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Total Fat (g)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Sat Fat (g)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Sodium</TH><TH borderColor=#000000 bgColor=#c0c0c0>Sugar (g)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Carbs (g)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Fiber (g)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Cholesterol(mg)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Potassium (mg)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Vitamin D (IU)</TH><TH borderColor=#000000 bgColor=#c0c0c0>Bread</TH><TH borderColor=#000000 bgColor=#c0c0c0>Meat</TH><TH borderColor=#000000 bgColor=#c0c0c0>Veg</TH><TH borderColor=#000000 bgColor=#c0c0c0>Fruit</TH><TH borderColor=#000000 bgColor=#c0c0c0>Milk</TH><TH borderColor=#000000 bgColor=#c0c0c0>FoodType</TH><TH borderColor=#000000 bgColor=#c0c0c0>Notes</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>Pizza - Cheese</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Schwans 78673</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1 slice</TD><TD borderColor=#d0d7e5 align=right>$0.45</TD><TD borderColor=#d0d7e5 align=right>300</TD><TD borderColor=#d0d7e5 align=right>15</TD><TD borderColor=#d0d7e5 align=right>350</TD><TD borderColor=#d0d7e5 align=right>1.8</TD><TD borderColor=#d0d7e5 align=right>300</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>11</TD><TD borderColor=#d0d7e5 align=right>3.5</TD><TD borderColor=#d0d7e5 align=right>630</TD><TD borderColor=#d0d7e5 align=right>15</TD><TD borderColor=#d0d7e5 align=right>37</TD><TD borderColor=#d0d7e5 align=right>4</TD><TD borderColor=#d0d7e5 align=right>15</TD><TD borderColor=#d0d7e5 align=right>190</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>2.25</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>0.125</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Protein</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5>Broccoli</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>NAT</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>3/4 cup</TD><TD borderColor=#d0d7e5 align=right>$0.21</TD><TD borderColor=#d0d7e5 align=right>33</TD><TD borderColor=#d0d7e5 align=right>3.51</TD><TD borderColor=#d0d7e5 align=right>53.82</TD><TD borderColor=#d0d7e5 align=right>0.94</TD><TD borderColor=#d0d7e5 align=right>1623.96</TD><TD borderColor=#d0d7e5 align=right>87.28</TD><TD borderColor=#d0d7e5 align=right>0.47</TD><TD borderColor=#d0d7e5 align=right>0.06</TD><TD borderColor=#d0d7e5 align=right>30.42</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>5.97</TD><TD borderColor=#d0d7e5 align=right>3.39</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>341.64</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Veg</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3</TD><TD borderColor=#d0d7e5>Strawberries</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>USDA</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1/2 cup</TD><TD borderColor=#d0d7e5 align=right>$0.26</TD><TD borderColor=#d0d7e5 align=right>122</TD><TD borderColor=#d0d7e5 align=right>0.68</TD><TD borderColor=#d0d7e5 align=right>14</TD><TD borderColor=#d0d7e5 align=right>0.75</TD><TD borderColor=#d0d7e5 align=right>31</TD><TD borderColor=#d0d7e5 align=right>52.8</TD><TD borderColor=#d0d7e5 align=right>0.17</TD><TD borderColor=#d0d7e5 align=right>0.01</TD><TD borderColor=#d0d7e5 align=right>4</TD><TD borderColor=#d0d7e5 align=right>30.61</TD><TD borderColor=#d0d7e5 align=right>33.05</TD><TD borderColor=#d0d7e5 align=right>2.4</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>125</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Fruit</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4</TD><TD borderColor=#d0d7e5>Milk - 1%</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>8 oz</TD><TD borderColor=#d0d7e5 align=right>$0.24</TD><TD borderColor=#d0d7e5 align=right>120</TD><TD borderColor=#d0d7e5 align=right>8.2</TD><TD borderColor=#d0d7e5 align=right>290</TD><TD borderColor=#d0d7e5 align=right>0.23</TD><TD borderColor=#d0d7e5 align=right>478</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>2.4</TD><TD borderColor=#d0d7e5 align=right>1.5</TD><TD borderColor=#d0d7e5 align=right>107</TD><TD borderColor=#d0d7e5 align=right>12.7</TD><TD borderColor=#d0d7e5 align=right>12.7</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>12.2</TD><TD borderColor=#d0d7e5 align=right>366</TD><TD borderColor=#d0d7e5 align=right>127</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>Milk</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5>Spaghetti</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Pasta, Taco Meat, Sauce (USDA recipe nutrients)</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1 cup</TD><TD borderColor=#d0d7e5 align=right>$0.35</TD><TD borderColor=#d0d7e5 align=right>295</TD><TD borderColor=#d0d7e5 align=right>15.84</TD><TD borderColor=#d0d7e5 align=right>20</TD><TD borderColor=#d0d7e5 align=right>2.42</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0.4</TD><TD borderColor=#d0d7e5 align=right>7.38</TD><TD borderColor=#d0d7e5 align=right>1.46</TD><TD borderColor=#d0d7e5 align=right>335</TD><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5 align=right>42.57</TD><TD borderColor=#d0d7e5 align=right>6</TD><TD borderColor=#d0d7e5 align=right>35</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Protein</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>6</TD><TD borderColor=#d0d7e5>Green Beans</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>USDA</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>3/4 cup</TD><TD borderColor=#d0d7e5 align=right>$0.20</TD><TD borderColor=#d0d7e5 align=right>21</TD><TD borderColor=#d0d7e5 align=right>1.17</TD><TD borderColor=#d0d7e5 align=right>27</TD><TD borderColor=#d0d7e5 align=right>0.915</TD><TD borderColor=#d0d7e5 align=right>441</TD><TD borderColor=#d0d7e5 align=right>4.8</TD><TD borderColor=#d0d7e5 align=right>0.105</TD><TD borderColor=#d0d7e5 align=right>0.03</TD><TD borderColor=#d0d7e5 align=right>210</TD><TD borderColor=#d0d7e5 align=right>0.975</TD><TD borderColor=#d0d7e5 align=right>4.56</TD><TD borderColor=#d0d7e5 align=right>1.95</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>111</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Veg</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>7</TD><TD borderColor=#d0d7e5>Orange</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1 orange</TD><TD borderColor=#d0d7e5 align=right>$0.17</TD><TD borderColor=#d0d7e5 align=right>62</TD><TD borderColor=#d0d7e5 align=right>1.23</TD><TD borderColor=#d0d7e5 align=right>52</TD><TD borderColor=#d0d7e5 align=right>0.13</TD><TD borderColor=#d0d7e5 align=right>295</TD><TD borderColor=#d0d7e5 align=right>69.7</TD><TD borderColor=#d0d7e5 align=right>0.16</TD><TD borderColor=#d0d7e5 align=right>0.02</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Fruit</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>8</TD><TD borderColor=#d0d7e5>Roll</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Homemade</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>2 oz</TD><TD borderColor=#d0d7e5 align=right>$0.04</TD><TD borderColor=#d0d7e5 align=right>175</TD><TD borderColor=#d0d7e5 align=right>4.42</TD><TD borderColor=#d0d7e5 align=right>29.27</TD><TD borderColor=#d0d7e5 align=right>1.7</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0.1</TD><TD borderColor=#d0d7e5 align=right>4.19</TD><TD borderColor=#d0d7e5 align=right>0.6</TD><TD borderColor=#d0d7e5 align=right>29</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Bread</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>10</TD><TD borderColor=#d0d7e5>Tunafish Sandwich on Bun</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Star Kist, Lite Hellmans & Merita WW</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1 sand</TD><TD borderColor=#d0d7e5 align=right>$0.38</TD><TD borderColor=#d0d7e5 align=right>180</TD><TD borderColor=#d0d7e5 align=right>19</TD><TD borderColor=#d0d7e5 align=right>60</TD><TD borderColor=#d0d7e5 align=right>1.44</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>5.5</TD><TD borderColor=#d0d7e5 align=right>0.5</TD><TD borderColor=#d0d7e5 align=right>630</TD><TD borderColor=#d0d7e5 align=right>4</TD><TD borderColor=#d0d7e5 align=right>18</TD><TD borderColor=#d0d7e5 align=right>3</TD><TD borderColor=#d0d7e5 align=right>30</TD><TD borderColor=#d0d7e5 align=right>170</TD><TD borderColor=#d0d7e5 align=right>40</TD><TD borderColor=#d0d7e5 align=right>1.3</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Protein</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>11</TD><TD borderColor=#d0d7e5>Sweet Potato Fries</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Homemade</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>6 oz</TD><TD borderColor=#d0d7e5 align=right>$0.25</TD><TD borderColor=#d0d7e5 align=right>214.8</TD><TD borderColor=#d0d7e5 align=right>2.89</TD><TD borderColor=#d0d7e5 align=right>47.63</TD><TD borderColor=#d0d7e5 align=right>2.9</TD><TD borderColor=#d0d7e5 align=right>34119</TD><TD borderColor=#d0d7e5 align=right>41.91</TD><TD borderColor=#d0d7e5 align=right>4.65</TD><TD borderColor=#d0d7e5 align=right>0.03</TD><TD borderColor=#d0d7e5 align=right>75</TD><TD borderColor=#d0d7e5 align=right>0.08</TD><TD borderColor=#d0d7e5 align=right>41.65</TD><TD borderColor=#d0d7e5 align=right>0.08</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>591</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Veg</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>12</TD><TD borderColor=#d0d7e5>Ketchup</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Simply Heinz</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>2 T</TD><TD borderColor=#d0d7e5 align=right>$0.06</TD><TD borderColor=#d0d7e5 align=right>40</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>380</TD><TD borderColor=#d0d7e5 align=right>8</TD><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>13</TD><TD borderColor=#d0d7e5>Mixed Fruit</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Del Monte</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1/2 cup</TD><TD borderColor=#d0d7e5 align=right>$0.20</TD><TD borderColor=#d0d7e5 align=right>80</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>200</TD><TD borderColor=#d0d7e5 align=right>1.2</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5 align=right>15</TD><TD borderColor=#d0d7e5 align=right>18</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>85</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Fruit</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>15</TD><TD borderColor=#d0d7e5>Meatloaf</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>USDA recipe ????</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1 slice</TD><TD borderColor=#d0d7e5 align=right>$0.41</TD><TD borderColor=#d0d7e5 align=right>195</TD><TD borderColor=#d0d7e5 align=right>17.05</TD><TD borderColor=#d0d7e5 align=right>47</TD><TD borderColor=#d0d7e5 align=right>2.23</TD><TD borderColor=#d0d7e5 align=right>154</TD><TD borderColor=#d0d7e5 align=right>2.9</TD><TD borderColor=#d0d7e5 align=right>10.29</TD><TD borderColor=#d0d7e5 align=right>4.22</TD><TD borderColor=#d0d7e5 align=right>122</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>8</TD><TD borderColor=#d0d7e5 align=right>1.3</TD><TD borderColor=#d0d7e5 align=right>67</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Protein</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>16</TD><TD borderColor=#d0d7e5>Mashed Potatoes - 3/4</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Simplot (True)</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>3/4 cup</TD><TD borderColor=#d0d7e5 align=right>$0.12</TD><TD borderColor=#d0d7e5 align=right>114</TD><TD borderColor=#d0d7e5 align=right>1.53</TD><TD borderColor=#d0d7e5 align=right>0.945</TD><TD borderColor=#d0d7e5 align=right>0.555</TD><TD borderColor=#d0d7e5 align=right>254.04</TD><TD borderColor=#d0d7e5 align=right>22.5</TD><TD borderColor=#d0d7e5 align=right>2.76</TD><TD borderColor=#d0d7e5 align=right>1.725</TD><TD borderColor=#d0d7e5 align=right>247.17</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>19.5</TD><TD borderColor=#d0d7e5 align=right>1.5</TD><TD borderColor=#d0d7e5 align=right>7.5</TD><TD borderColor=#d0d7e5 align=right>0.93</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Veg</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>17</TD><TD borderColor=#d0d7e5>Cherries</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>USDA </TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1/2 cup</TD><TD borderColor=#d0d7e5 align=right>$0.20</TD><TD borderColor=#d0d7e5 align=right>84</TD><TD borderColor=#d0d7e5 align=right>0.76</TD><TD borderColor=#d0d7e5 align=right>11.34</TD><TD borderColor=#d0d7e5 align=right>0.5</TD><TD borderColor=#d0d7e5 align=right>197</TD><TD borderColor=#d0d7e5 align=right>4.66</TD><TD borderColor=#d0d7e5 align=right>0.25</TD><TD borderColor=#d0d7e5 align=right>0.04</TD><TD borderColor=#d0d7e5 align=right>3.78</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>21.8</TD><TD borderColor=#d0d7e5 align=right>0.88</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>186.48</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Fruit</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>20</TD><TD borderColor=#d0d7e5>Chicken Nuggets</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Goldkist</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>5 nuggets</TD><TD borderColor=#d0d7e5 align=right>$0.28</TD><TD borderColor=#d0d7e5 align=right>174</TD><TD borderColor=#d0d7e5 align=right>14</TD><TD borderColor=#d0d7e5 align=right>30</TD><TD borderColor=#d0d7e5 align=right>1.98</TD><TD borderColor=#d0d7e5 align=right>150</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>9</TD><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5 align=right>461</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>12</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>45</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Protein</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>21</TD><TD borderColor=#d0d7e5>Ketchup</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Simply Heinz</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>2 T</TD><TD borderColor=#d0d7e5 align=right>$0.06</TD><TD borderColor=#d0d7e5 align=right>40</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>380</TD><TD borderColor=#d0d7e5 align=right>8</TD><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>22</TD><TD borderColor=#d0d7e5>Veggie Chili</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>USDA (for now)</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1 cup (?)</TD><TD borderColor=#d0d7e5 align=right>$0.30</TD><TD borderColor=#d0d7e5 align=right>223</TD><TD borderColor=#d0d7e5 align=right>14.57</TD><TD borderColor=#d0d7e5 align=right>333</TD><TD borderColor=#d0d7e5 align=right>2.26</TD><TD borderColor=#d0d7e5 align=right>1257</TD><TD borderColor=#d0d7e5 align=right>17.5</TD><TD borderColor=#d0d7e5 align=right>7.48</TD><TD borderColor=#d0d7e5 align=right>3.76</TD><TD borderColor=#d0d7e5 align=right>606</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>27.02</TD><TD borderColor=#d0d7e5 align=right>6.4</TD><TD borderColor=#d0d7e5 align=right>17</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Veg</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>23</TD><TD borderColor=#d0d7e5>Banana - Med</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>Raw - whole banana</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>1/2 cup</TD><TD borderColor=#d0d7e5 align=right>$0.20</TD><TD borderColor=#d0d7e5 align=right>105</TD><TD borderColor=#d0d7e5 align=right>4.3</TD><TD borderColor=#d0d7e5 align=right>5.9</TD><TD borderColor=#d0d7e5 align=right>0.3</TD><TD borderColor=#d0d7e5 align=right>75.5</TD><TD borderColor=#d0d7e5 align=right>10.3</TD><TD borderColor=#d0d7e5 align=right>0.4</TD><TD borderColor=#d0d7e5 align=right>0.1</TD><TD borderColor=#d0d7e5 align=right>1.2</TD><TD borderColor=#d0d7e5 align=right>14.4</TD><TD borderColor=#d0d7e5 align=right>27</TD><TD borderColor=#d0d7e5 align=right>3.1</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>422</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5>Fruit</TD><TD borderColor=#d0d7e5>
</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>24</TD><TD borderColor=#d0d7e5>Milk - Skim</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>
</TD><TD borderColor=#d0d7e5>8 oz</TD><TD borderColor=#d0d7e5 align=right>$0.24</TD><TD borderColor=#d0d7e5 align=right>80</TD><TD borderColor=#d0d7e5 align=right>8</TD><TD borderColor=#d0d7e5 align=right>300</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>500</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>120</TD><TD borderColor=#d0d7e5 align=right>12</TD><TD borderColor=#d0d7e5 align=right>12</TD><TD borderColor=#d0d7e5 align=right>0</TD><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5 align=right>366</TD><TD borderColor=#d0d7e5 align=right>100</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>
</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>Milk</TD><TD borderColor=#d0d7e5>
</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I would like to create a query or filter that provide a list of all acceptable meals. An acceptable meal is at least 1 bread, 1 meat, 1 veg, 1 fruit & 1 milk. An acceptable meal is also in between 550-650 calories and has over 5000 IU's of Vit A. (there's more, but let's start there).

How do I create a filter or query that says:

1.) IF Bread > 1, Meat > 1, Veg > 1,> 1, Fruit > 1, Milk > 1,
2.) THEN calories = 550-650
3.) THEN Vit A > 5000

And so on, and will return to me a list of all possible meal combinations that meet all the requirements?

Thanks so much! (I'm not being neurotic here. This is for work - institutional food service.)
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
How to structure a variable product rating database
Replies
5
Views
935
mdmilner
M
N
  • Question Question
Bring nutrition values across from food selected from dropdown list
Replies
1
Views
251
NateSC
NateSC
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
466
RoryA
RoryA
S
  • Question Question
Repeating a pivot table many times and changing only one column -- are there more efficient approaches?
Replies
0
Views
462
sschaefer
S
A
  • Question Question
Automation of Copy and Merge Data in Column A and B of all files in a folder. Order of Data Differs
Replies
2
Views
712
ashish002
A

Forum statistics

Threads
1,224,592
Messages
6,179,790
Members
452,942
Latest member
VijayNewtoExcel

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top