This is a manufactured inventory question.

Table 1 has the fields:

WidgetName

WidgetCost

Portal - Table 2::IngredientName IngredientQty

Table 2 has the fields:

WidgetName

IngredientName

IngredientQty

There is a relationship between the two tables:

WidgetName=WidgetName

So using the relationship, I am able to get the Portal ingredient list in Table 1 working.

What I need to do is calculate the total cost of all the ingredients. To do this, I need to look in Table 1 and find the record where the WidgetName=IngredientName. Then use WidgetCost field and multiply it by the IngredientQty field in Table 2.

I can't just use the WidgetName relationship because it is already being used to create the portal in Table 1.

Anyone able to help with the syntax for that?

[color:green]I may have resolved this. What I did was go to relationships and use the ++ button to make a copy of Table 1. I then related the WidgetName and IngredientName fields and this allowed me to just select the WidgetCost field for use in the calculation.

Is this the best way to do this? Does this make a copy of the whole database, or is it just a new index to make a relationship from? Is this efficient?

Edited September 3, 200916 yr by Guest
May have resolved it

Please take a step back and review the use of key fields to relate tables. Each table should have a unique ID field, and that is the field that you use to relate tables--never a field such as a name.

How about viewing this (and other) tutorials FM Tutorial on Relating Tables .

In your case, each Widget would have an ID. The ingredients table would also have an ID. But since an ingredient can be used in more than one Widget, you need a third table that stores these combinations. This is called a join table. It would contain the WidgetID and the IngredientID, Qty and perhaps Cost. Then a calc field Ttl_Cost = Qty * Cost.

Then in Widgets, you would have a calc field, Widget_Cost, that equals the sum of the Tot_Cost of the widgets.