Establishing Limits

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! :)
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

revans

Well-known Member
Joined
Apr 5, 2010
Messages
576
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
 

nikik

New Member
Joined
Apr 7, 2011
Messages
21
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!
 

revans

Well-known Member
Joined
Apr 5, 2010
Messages
576

ADVERTISEMENT

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
 

nikik

New Member
Joined
Apr 7, 2011
Messages
21
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:
 

nikik

New Member
Joined
Apr 7, 2011
Messages
21

ADVERTISEMENT

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!
 

nikik

New Member
Joined
Apr 7, 2011
Messages
21
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!
 

nikik

New Member
Joined
Apr 7, 2011
Messages
21
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.)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,902
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top