One Layout/Table to show records from 2 other tables

[Matt Leach]

Im working on a recipe database and am coming up on the last phase of the project and am drawing a blank on how to accommodate the request.

I need a layout to compare information from 2 tables. The tables are:

1. Goods_In_Ingredients

2. Production_Ingredients.

Im not sure how to setup a relationship for this scenario or if it is even possible. Here is a bit of details outlining what i'm trying to do.

I need to have 2 drops down lists on the layout. One for "Ingredients" and one for "Batch_Code".

Based on what is selected from these drop downs, the following needs to happen:

1. All of the items in the Goods_In_Ingredients table that have the ingredient and batch code selected from the drop down need to be totaled up (based on an amount field for each record)

2. All of the items in the Production_Ingredients table that have the ingredient and batch code selected from the drop down need to be totaled up (based on an amount field for each record)

Would i have to create a new table and create a relationship to these two tables somehow or could i just set the layout to reference one of the tables that already has a relationship to the other.

I hope i was able to explain clearly enough as i'm going by what was provided to me. If it helps to clear up any confusion i can provide a copy of the file.

Thanks

[comment]

What is "Goods_In_Ingredients"?

This seems to be a follow up of:

http://fmforums.com/forum/topic/79997-copy-fields-from-one-table-to-another/page__p__372203#entry372203

but I don't recognize the term.

[Matt Leach]

It is a follow up of that thread but i figured since it was more of a relationship question i would post it here.

There is a table called Goods_In which represents a shipment that was received. The shipment details get entered into this table, and each ingredient that is included in the shipment is entered as a line item into the Goods_In_Ingredient table.

[comment]

OK, that sounds like PurchaseOrders and POLineItems. Why would there be a BatchID in this table?

[Matt Leach]

That's pretty much the same idea.

As far as why that table contains the BatchID, i do not know as this was in place before i started working on the project.

The basic purpose of the layout is to compare, for a particular ingredient with a specific batch number, how many kgs were delivered vs. how many kgs were used in production

[comment]

Are we talking about your batch or the batch of the supplier/manufacturer of the ingredient?

[Matt Leach]

It would be our batch.

[comment]

I am afraid I don't get this. Say you receive 10 units of material A. You use 4 of those to produce batch 123, and another 4 to produce batch 125.

Do you see why the batch ID cannot be in POLineItems table?

[Matt Leach]

I understand why given that example. Im going to have to get more information from them to get a better idea of how they come up with the batch numbers, etc..