Relationship report Help

[MurilloReis]

Hi,

I am having a big problem trying to figure out wich way i can make this work,

I have here a

-contacts table (contatos)

-Sales table (vendas)

-Purchases Table (compras)

-Sales Line items (Lista Vendas)

-Purchases Line items (Lista Compras)

-Produts Table (Produtos)

Now, the img attached shows the relationship, but my need is the inventory, i seem to not get the result i want and i have figured out the problem, i just dont have the answer.

1st try::

What i tried to do:

Make a quantity field inside the products table calculating the quantities from the sales list minus (-) the purchases list to get the result.

What went wrong:

Since the sales and purchases list is linked to a client ID, the product inventory only shows the calculation from each client but not from each product independently.

2nd Try::

What i tried to do:

Making an inventory table with the quantity calculation using the product id as a child on the table.

What went wrong:

I have no idea...

If anyone has any clue on what i am doing wrong,

Please help me out. I really need to get this going.

If best, add my on my msn account:

[email protected]

Thanks for the attention and all your help.

Regards

[RalphL]

Try making a new Product TO and relate it to a new Sales Line Item TO and a New Purchase Line Item TO. Use ProductID as the key in both relationships. Base your calculation on these relationships.

[MurilloReis]

well,

I dont really get the concept...

do i create a new field on products called product TO and relate it to the sales and purchases TO's, if so wich field do i use as a calculation?

maybe a sketch as on how i do it would be more direct?

Sry, for my missunderstanding.

Thank you

Edited March 19, 2009 by Guest

[RalphL]

It is just another TO to your existing table. the other TO's are also to existing tables.

[MurilloReis]

what kind of information are the TO's going to store? and wich field do i use as my calculation?

thx!

[MurilloReis]

What kind of information are the TO's going to store? and wich field is going to be my calculation field :

sry,

Thx

[RalphL]

TO's are pointers to tables. You can have many TO's pointing to one table. The field is the quantity of product in the inventory.

[MurilloReis]

ok, i created a TO for my products table, sales list table, and purchase table, then i related all 3 to each other, my key field remains product id now linkd to product TO, i left the to's all to show a value of 1 on creation. but still nothing happend.

am i doing this right ??

[RalphL]

You need to redefine the calculation. Make sure that you are in the new Products TO when you define this calculation. At the top of the dialog box for defining fields is a Line of text that reads: "Evaluate this calculation from the context of:" then the name of the TO.