4 Replies Latest reply on May 27, 2015 11:58 PM by khasim686

need help with item distribution calculation

Title

need help with item distribution calculation

Post

dear all

i have 3 tables 1.employees 2.items 3.distribution and each table have couple of records

i wanna distribute sum items to the employees

for example

table1.employess

id        name

001     employee1

002     employee2

003     employee3

table2.items

id       name    desciption   quantity

101    item1                         50

102    item2                         35

103    item3                         90

104    item4                         75

table3.distribution

employee_id_fk      item_id_fk     quantity

001                           101                2

001                           102                1

002                           102                2

003                           103                1

001                           101                1

002                           103                3

i would like to know the total no of items distributed and items available in stock

• 1. Re: need help with item distribution calculation

Define a relationship linking Table 2::id to Table 3::Item_id_fk.

In table 2, Sum (table 3::quantity ) will show the total quantity distributed.

And in the same table, quantity - sum ( Table 3::quantity ) will compute the total remaining--the Qty on Hand.

• 2. Re: need help with item distribution calculation

but i would like to know the quantity distributed for each item

for ex: in my layout1

item_id        item_name          quantity_in      quantity_distributed        quantity_available

101                item1                    50                           3                                    47

102                item2                    35                           3                                    32

103                item3                    90                           4                                    86

• 3. Re: need help with item distribution calculation

Which is exactly what I had in mind with that solution. You get a different total for each item as each record in Table 2 only matches to items in Table 3 with the same ID and the sum function will only total up those records with the same ID.

• 4. Re: need help with item distribution calculation

@PhilModJunk    thank u , thanks a lot