Establishing Limits

[nikik]

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!

 

[Phildaburn]

Use these 5 queries as a foundation:
qryProtein =

SELECT tblFoods.*
FROM tblFoods
WHERE (((tblFoods.FoodType)="Protein"));

qryVegetable =

SELECT tblFoods.*
FROM tblFoods
WHERE (((tblFoods.FoodType)="veg"));

qryMilk =

SELECT tblFoods.*
FROM tblFoods
WHERE (((tblFoods.FoodType)="milk"));

qryGrain =

SELECT tblFoods.*
FROM tblFoods
WHERE (((tblFoods.FoodType)="Bread"));

qryFruit =

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

Then, use this query to provide all your data combinations:

SELECT qryFruit.FoodName, qryFruit.FoodType, qryFruit.Calories, qryFruit.Bread, qryFruit.Meat, qryFruit.Veg, qryFruit.Fruit, qryFruit.Milk, qryGrain.FoodName, qryGrain.FoodType, qryGrain.Calories, qryGrain.Bread, qryGrain.Meat, qryGrain.Veg, qryGrain.Fruit, qryGrain.Milk, qryMilk.FoodName, qryMilk.FoodType, qryMilk.Calories, qryMilk.Bread, qryMilk.Meat, qryMilk.Veg, qryMilk.Fruit, qryMilk.Milk, qryProtein.FoodName, qryProtein.FoodType, qryProtein.Calories, qryProtein.Bread, qryProtein.Meat, qryProtein.Veg, qryProtein.Fruit, qryProtein.Milk, qryVegetable.FoodName, qryVegetable.FoodType, qryVegetable.Calories, qryVegetable.Bread, qryVegetable.Meat, qryVegetable.Veg, qryVegetable.Fruit, qryVegetable.Milk, [qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories] AS TotalCalories, [qryProtein].[Bread]+[qryGrain].[Bread]+[qryVegetable].[Bread]+[qryFruit].[Bread]+[qryMilk].[Bread] AS TotalBread, [qryProtein].[Meat]+[qryGrain].[Meat]+[qryVegetable].[Meat]+[qryFruit].[Meat]+[qryMilk].[Meat] AS TotalMeat, [qryProtein].[Veg]+[qryGrain].[Veg]+[qryVegetable].[Veg]+[qryFruit].[Veg]+[qryMilk].[Veg] AS TotalVeg, [qryProtein].[Fruit]+[qryGrain].[Fruit]+[qryVegetable].[Fruit]+[qryFruit].[Fruit]+[qryMilk].[Fruit] AS TotalFruit, [qryProtein].[Milk]+[qryGrain].[Milk]+[qryVegetable].[Milk]+[qryMilk].[Milk]+[qryFruit].[Milk] AS TotalMilk, [qryProtein].[Vit A (IU)]+[qryGrain].[Vit A (IU)]+[qryVegetable].[Vit A (IU)]+[qryMilk].[Vit A (IU)]+[qryFruit].[Vit A (IU)] AS TotalVitA
FROM qryFruit, qryGrain, qryMilk, qryProtein, qryVegetable
WHERE ((([qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories])>550 And ([qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories])<650) AND (([qryProtein].[Bread]+[qryGrain].[Bread]+[qryVegetable].[Bread]+[qryFruit].[Bread]+[qryMilk].[Bread])>=1) AND (([qryProtein].[Meat]+[qryGrain].[Meat]+[qryVegetable].[Meat]+[qryFruit].[Meat]+[qryMilk].[Meat])>=1) AND (([qryProtein].[Veg]+[qryGrain].[Veg]+[qryVegetable].[Veg]+[qryFruit].[Veg]+[qryMilk].[Veg])>=1) AND (([qryProtein].[Fruit]+[qryGrain].[Fruit]+[qryVegetable].[Fruit]+[qryFruit].[Fruit]+[qryMilk].[Fruit])>=1) AND (([qryProtein].[Milk]+[qryGrain].[Milk]+[qryVegetable].[Milk]+[qryMilk].[Milk]+[qryFruit].[Milk])>=1) AND (([qryProtein].[Vit A (IU)]+[qryGrain].[Vit A (IU)]+[qryVegetable].[Vit A (IU)]+[qryMilk].[Vit A (IU)]+[qryFruit].[Vit A (IU)])>=5000));

Using the data provided in the example actually yielded no returned menu combinations since the Vit A total was less than the 5000. I assume that there are additional records to your data that could provide a Vit A total high enough to meet your criteria.

Hope this helps.

Phil...

P.S. I noticed Ketchup appeared in your data more than once however since a food type was not assigned to it, it was not pulled into your results. Having duplicates in your table could cause problems. Please try to avoid duplicates.

 

[Phildaburn]

This may be a better view of the final data for you using this query (to show results, i changed the Vit A criteria to >=500):

SELECT qryFruit.FoodName AS Fruit, qryGrain.FoodName AS Grain, qryMilk.FoodName AS Milk, qryProtein.FoodName AS Protein, qryVegetable.FoodName AS Veg, [qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories] AS TotalCalories, [qryProtein].[Bread]+[qryGrain].[Bread]+[qryVegetable].[Bread]+[qryFruit].[Bread]+[qryMilk].[Bread] AS TotalBread, [qryProtein].[Meat]+[qryGrain].[Meat]+[qryVegetable].[Meat]+[qryFruit].[Meat]+[qryMilk].[Meat] AS TotalMeat, [qryProtein].[Veg]+[qryGrain].[Veg]+[qryVegetable].[Veg]+[qryFruit].[Veg]+[qryMilk].[Veg] AS TotalVeg, [qryProtein].[Fruit]+[qryGrain].[Fruit]+[qryVegetable].[Fruit]+[qryFruit].[Fruit]+[qryMilk].[Fruit] AS TotalFruit, [qryProtein].[Milk]+[qryGrain].[Milk]+[qryVegetable].[Milk]+[qryMilk].[Milk]+[qryFruit].[Milk] AS TotalMilk, [qryProtein].[Vit A (IU)]+[qryGrain].[Vit A (IU)]+[qryVegetable].[Vit A (IU)]+[qryMilk].[Vit A (IU)]+[qryFruit].[Vit A (IU)] AS TotalVitA
FROM qryFruit, qryGrain, qryMilk, qryProtein, qryVegetable
WHERE ((([qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories])>550 And ([qryProtein].[Calories]+[qryGrain].[Calories]+[qryVegetable].[Calories]+[qryFruit].[Calories]+[qryMilk].[Calories])<650) AND (([qryProtein].[Bread]+[qryGrain].[Bread]+[qryVegetable].[Bread]+[qryFruit].[Bread]+[qryMilk].[Bread])>=1) AND (([qryProtein].[Meat]+[qryGrain].[Meat]+[qryVegetable].[Meat]+[qryFruit].[Meat]+[qryMilk].[Meat])>=1) AND (([qryProtein].[Veg]+[qryGrain].[Veg]+[qryVegetable].[Veg]+[qryFruit].[Veg]+[qryMilk].[Veg])>=1) AND (([qryProtein].[Fruit]+[qryGrain].[Fruit]+[qryVegetable].[Fruit]+[qryFruit].[Fruit]+[qryMilk].[Fruit])>=1) AND (([qryProtein].[Milk]+[qryGrain].[Milk]+[qryVegetable].[Milk]+[qryMilk].[Milk]+[qryFruit].[Milk])>=1) AND (([qryProtein].[Vit A (IU)]+[qryGrain].[Vit A (IU)]+[qryVegetable].[Vit A (IU)]+[qryMilk].[Vit A (IU)]+[qryFruit].[Vit A (IU)])>=500));

It yielded 61 records. Here are the first few:
<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Query1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Fruit</TH><TH bgColor=#c0c0c0 borderColor=#000000>Grain</TH><TH bgColor=#c0c0c0 borderColor=#000000>Milk</TH><TH bgColor=#c0c0c0 borderColor=#000000>Protein</TH><TH bgColor=#c0c0c0 borderColor=#000000>Veg</TH><TH bgColor=#c0c0c0 borderColor=#000000>TotalCalories</TH><TH bgColor=#c0c0c0 borderColor=#000000>TotalBread</TH><TH bgColor=#c0c0c0 borderColor=#000000>TotalMeat</TH><TH bgColor=#c0c0c0 borderColor=#000000>TotalVeg</TH><TH bgColor=#c0c0c0 borderColor=#000000>TotalFruit</TH><TH bgColor=#c0c0c0 borderColor=#000000>TotalMilk</TH><TH bgColor=#c0c0c0 borderColor=#000000>TotalVitA</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>Orange</TD><TD borderColor=#d0d7e5>Roll</TD><TD borderColor=#d0d7e5>Milk - Skim</TD><TD borderColor=#d0d7e5>Spaghetti</TD><TD borderColor=#d0d7e5>Broccoli</TD><TD borderColor=#d0d7e5 align=right>645</TD><TD borderColor=#d0d7e5 align=right>3</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>2418.96</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Strawberries</TD><TD borderColor=#d0d7e5>Roll</TD><TD borderColor=#d0d7e5>Milk - 1%</TD><TD borderColor=#d0d7e5>Tunafish Sandwich on Bun</TD><TD borderColor=#d0d7e5>Broccoli</TD><TD borderColor=#d0d7e5 align=right>630</TD><TD borderColor=#d0d7e5 align=right>3.3</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>2132.96</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Orange</TD><TD borderColor=#d0d7e5>Roll</TD><TD borderColor=#d0d7e5>Milk - 1%</TD><TD borderColor=#d0d7e5>Tunafish Sandwich on Bun</TD><TD borderColor=#d0d7e5>Broccoli</TD><TD borderColor=#d0d7e5 align=right>570</TD><TD borderColor=#d0d7e5 align=right>3.3</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>2396.96</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Mixed Fruit</TD><TD borderColor=#d0d7e5>Roll</TD><TD borderColor=#d0d7e5>Milk - 1%</TD><TD borderColor=#d0d7e5>Tunafish Sandwich on Bun</TD><TD borderColor=#d0d7e5>Broccoli</TD><TD borderColor=#d0d7e5 align=right>588</TD><TD borderColor=#d0d7e5 align=right>3.3</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>2301.96</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>Cherries</TD><TD borderColor=#d0d7e5>Roll</TD><TD borderColor=#d0d7e5>Milk - 1%</TD><TD borderColor=#d0d7e5>Tunafish Sandwich on Bun</TD><TD borderColor=#d0d7e5>Broccoli</TD><TD borderColor=#d0d7e5 align=right>592</TD><TD borderColor=#d0d7e5 align=right>3.3</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5 align=right>2298.96</TD></TR></TBODY><TFOOT></TFOOT></TABLE>