7 Replies Latest reply on Aug 25, 2012 10:12 AM by philmodjunk

# Calculations with related tables

### Title

Calculations with related tables

### Post

• ###### 1. Re: Calculations with related tables

Your English is excellent but many details are lacking from your post. We do not know all the procedures your business uses to calculate these costs. We do not know what tables and relationships already exist in your database. If you haven't yet created any, then you need to describe what you would do if you simply wrote it all out on paper records.

• ###### 2. Re: Calculations with related tables

Well the procedure its very simple we register a provider in a specific providers table (fields:name, business name, client number) after we register the provider we procede to register all the materials he ships to us in a separate materials table with the fields: name of the material, type of material, cost per ton (we establish that cost) and provider (related field with the provider table) and last we have another table for the registry of receptions in that table we have the fields: date, number of reception, brute tare and net weight of the shipment (net weight its the one that really matters), provider (related field with the provider table), cost per reception and a material and cost per ton field, what i want its that when i choose the provider the materials we have registered to him and their respective costs appear in the material and cost per ton field then when thats done the cost per reception will take the cost per ton and multiply it for the net weight registered.

Hope this is more clear for you to help me and as always lof of thanks.

• ###### 3. Re: Calculations with related tables

So you have this relationship:

Providers----<Materials

Providers::Provider = Materials::Provider

and last we have another table for the registry of receptions

I would read that as a table for logging (registering) each shipment as it is received. Correct?

Seems like you need two tables for this, one where you have one record for each shipment received and one that lists the materials in that shipment. Or does each shipment consist of only one of the listed materials?

You might then have these additional relationships:

Providers----<Reception----<ReceivedMaterials>------ProviderMaterials   (Provider materials is a second occurence of materials.)

Reception would record the Provider, the date and time received and so forth. A portal to ReceivedMaterials would then list each material received in that shipment.

I think you are asking for two things:

1) a conditional value list that lists only the materials for the selected provider.

2) a "lookup" that copies the price per ton (Unit price) from Materials into a field in this table so that calculations can use the quantity recieved and this unit price to compute a cost for this shipment of material.

All of this can be done. Let me know if I have it correct or not and I'll provide the needed details.

Note if my relationship notations are new to you and you can't understand them, see this thread: Common Forum Relationship and Field Notations Explained

• ###### 4. Re: Calculations with related tables

Yes! i think you're right that's the two things i need.

Here's a picture of the relations i created.

• ###### 5. Re: Calculations with related tables

I can't read Spanish, but those relationships do not look correct.

You haven't answered a key question:

Does a shipment consist of a single material or a combination of multiple materials from the same provider?

• ###### 6. Re: Calculations with related tables

Proveedor=Provider

Residuo, nombre del residuo = Material

Costo por tonelada, costo aplicable = Cost per ton of material

The relations are that Proveedor from the Residuos and the Pesos table are the same thing but they cant register any new provider, for that we have the Proveedor table that table is related to the Proveedor field by the field Nombre corto (short name of the provider).

The table Residuos has the field Nombre del Residuo (name of the material) as i said in the past post thats the table where we register the materials we receive Nombre del residuo has a relation with the Pesos table (the table where we register the shipments) with the field residuo (material) the same thing happens with the cost per ton field.

And the answer is yes we can receive different materials in one shipment but we register them separately because we weight them separately.

• ###### 7. Re: Calculations with related tables

I still recomend the table structure and relationships that I posted earlier:

One record in Reception for each shipment received from a given provider. The date, provider and any other data (such as a total cost) that applies to the total shipment are recorded or calculate in the Reception table. (I'd call it Shipments.) Then a portal to ReceivedMaterials lists each material recieved and it's weight in individual portal records. One key advantage to this setup is that you only need select the provider once for each shipment instead of one for each material received.

The relationships used would look like this:

Providers::ProviderID = Reception::ProviderID

Boldface fields in the above list of match fields would be defined as auto-entered serial numbers and serve as the primary key for the table in which they are defined. The fields of same name listed on the right are number fields and serve as foreign key fields.