Autolookup Query to find Multiple Records

[Lee]

I am using Access 2007 and I have created meal plans which are composed of
multiple records. Each record has a text field named "Group" which
identifies the particular meal plan. There are multiple records within each
plan that are assigned the same "Group". The key field is an autonumber. I
would like to assign a particular meal plan to a particular date by using the
"Group" identifier in an autolookup query. Each meal plan can be assigned to
multiple dates. Unfortunately, since the "Group" identifier is not unique in
the group of records the query does not work. Is it possible to create an
autolookup query that will fill-in multiple records based upon the "Group"
identifier for a particular date?

 

[KARL DEWEY]

You need two tables - the 'group' the defines meal plans and a second one
that has dates, meal identifier (B, L, D or as some say B, D, S), and group.

 

[Lee]

Karl,

I have three tables with the following structures:

A) tblDietPlanGroup:
1) one text field that identifies the meal plans and takes a letter
such as "A", "B", "C", etc.
2) has a one-to-many relationship with tblDietPlan

B) tblDietPlan:
1) ID field- one autonumber filed as the key
2) Group- text field which takes a single letter that associates it with
the meal plan
3) Meal- number field which identifies the meal for the day such as "1",
"2", "3", etc.
4) Food- autolookup text field that fills in nutritional data from
tblFood after a food item has been selected from a combo box
5) Amount- number field to enter the amount of food eaten and is used to
determine the kilocalories, protein, fat, carbohydrates, fiber and sodium

C) tblFood- contains nutritional data for each food item and has the fields:
1) food item- ID field
2) protein
3) fat
4) carbohydrates
5) kilocalories
6) fiber
7) sodium
8) factor- used as quantity to be multiplied by the "Amount" field in
tblDietPlan
9) has a one-to-many relationship with tblDietPlan

Now, in my Meal Plan form, I have an autolookup field that allows me to type
in the Group letter and a subform displays all

the records containing all the foods and nutritional data for each meal

I would like to be able to create another form that has a date field which
is unique and a field for the group which is not

unique. When a group letter is entered, the subform is then populated with
all of the data for that meal plan.

 

[KARL DEWEY]

tblFood- does not have a field for food title - Hot Cereal, Oatmeal - Fried
Chicken - Mashed Potatoes with Gravy - etc.

Add a date field to your tblDietPlan.

Your scheme allows thre plans with three meals per plan. Not much variety.