Lookup with multiple Fields

[CHess]

I am not sure if I am using the correct method or not.

I have two tables. One table has food items along with the calorie, carb,
and protein count.

In the other table, (daily intake) I want to enter in a food item and have
all of the other values brought in based on the food item.

I created a lookup on the food item - now in the drop down, I see the food
tiem, calories, carbs and proteins, but these values will not come into the
fields in the dailyintake field. How do I have that lookup also map those
values to the corresponding fields in the daily intake table?

Thank you!

 

[Allen Browne]

You do not have those fields in the [daily intake] table.

Presumably you have these tables:
a) FoodItem table, with fields like this:
FoodItemID AutoNumber primary key
FoodItem Text name of this item
Calorie Number number of calories per gram

b) DailyIntake table, with fields like this:
DailyIntakeID AutoNumber primary key
ClientID Number the person who had this intake
IntakeDate Date/Time the date when the person ate this.
FoodItemID relates to the primary key of your food item table.
Quantity Number (Double) how many grams.

Now create a query that uses both tables. The query will calculate the
values for you. Just type an expression into the Field row in query design,
e.g.:
[Quantity] * [Calorie]

For more info, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Beetle]

First, I might be wrong, but it sounds like you are working directly in the
tables, which you shouldn't be. You should be using forms/subforms to view or
enter information in your db.

Second, you do not want to store the same information in two separate
tables. The data you need can be retrieved from the first table whenever you
need it. This can be done a few different ways (DLookup function, column
property of a combo box, etc.), but first you should create a form (or forms)
if you haven't already.

I usually create a query based on the table, then base the form on the
query. This makes it easier to sort your data the way you want, or perform
calculations if you need to.

HTH

 

[Hunter57]

Hi CHess,

I agree with Allen Browne and Bettle.

Here is what you can do with the Forms you need.

You only have to store the tblFoodItems ID in your tblDailyIntake.
By using this ID number the rest of the information can be displayed on your
form.
The information is stored one time in the tables and displayed on your form.

You will need at least two Forms: frmFoodItems, frmDailyIntake

Form frmFoodItems:
RecordSource tblFoodItems or a Query

Form frmFoodItems Controls:
TextBoxes: txtFoodItem, txtFICalories, txtFICarbs, txtFIProtein


Form frmDailyIntake:
RecordSource tblDailyIntake or a Query

Form frmDailyIntake Controls:

Bound TextBoxes: txtQuantity, txtDIDate, txtDITime, Or just txtIntakeDate if
you don't need the time.

To display the information from your other table on your frmDailyIntake Form
you can use the following:

Combo Box: cboFoodItem

Use the combo box Properties sheet to add the following settings:

Control Source: FoodItemID
Column Count: 5
Column Widths: 0";1";1";1";1" The first 0" will hide the FoodItemID and
display the FoodItem name in the combo box.
List Width: 1"
RowSource:
SELECT DISTINCT FoodItemID, FoodItem, FICalories, FICarbs, FIProtein
FROM tblFoodItems
ORDER BY FoodItem

*Unbound* Text Boxes: txtFICalories, txtFICarbs, txtFIProtein

Adding the following will display the Calories, Carbs, and Protein
information on your Form:
txtFICalories RecordSource: =cboFoodItem.Column(2)
txtFICarbs RecordSource: =cboFoodItem.Column(3)
txtFIProtein RecordSource: =cboFoodItem.Column(4)