manage data from table to new table...

[xbandx]

Problem:

I would like to create/compile a table with Text data from other table..

firt table:

recipeT Type Qty

recipe1 food1 100

recipe1 food2 200

recipe1 food3 300

recipe1 food4 400

recipeT Type Qty

recipe2 food1 100

recipe2 food2 200

in the new Table:

firt table:

Type Qty

food1 100

food2 200

food3 300

food4 400

food1 100 ]-from second recipe

food2 200 ]

and after, aggregate te result

food1 200

food2 400

food3 300

food4 400

How can do that?, I have the first table that work well...but I don't know how can create the new table..

help

[stefwef]

To recap: You currently have only ONE table with fields recipe, food and #?

You want to get a total of # for each food (regardless of recipe)?

Do you want this as a dynamic value (you can see in browse mode) or is a printed report sufficient?

(See recent posting about fruit.)

[comment]

I believe you should have THREE tables: Recipes, Foods (or Products) and Quantities. The Quantities table is what you have now, except there should be IDs instead of names:

RecipeID   ProductID   Qty

1             1        100

1             2        200

1             3        300

1             4        400

2             1        100

2             2        200

This way you can summarize the quantities of each product by summing up the related records in the Quantities table.

Alternatively, as stefwef said, you can summarize your current table by food.

[xbandx]

tanks,

for semplicity, i have the rule that a recipe can be converted into a ingredient and so on.

is possible to extract the single ingredient from the "master" recipe?

regards recipe RCT00002:

the composition is:

ING 0019 (but this is the recipe RCT00003)

ING 0128

ING 0038

the goal is to print the food label...hard goal for me.

thx a lot for your help!!!

newsricette_copy1.zip

[comment]

Basically, you are asking for a bill-of-materials. This is not an easy task, I'm afraid.

First, if a recipe can also serve as an ingredient for another recipe, then recipes and ingredients should be in the same table, self-joined by a second table of Quantities. However, this is only the beginning - the real problem is how to get the list of basic ingredients only for any recipe, recursively. There have been some previous posts on the subject, for example:

http://fmforums.com/forum/topic/54844-bill-of-materials-formula/