Inventory Balance

[josros60]

I have a product inventory database same product provided from 2 or 3 suppliers, so i create in a product table a vendor product number and company product number, the idea is to bring one balance in a form or report for a product from 3 different vendor but being same product.

I cannot figure out how to do a query to calculate that.
for example:
These are the fields name:

Product VendorProductNumber CompanyProductnumber Supplier


thank you

 

[Oeldere]

I don't undertand what you trying to calculate.

Maybe with an example?

 

[josros60]

ok,

for example i bought 5 boxes of shampoo 2 boxes from 1 supplier and 3 boxes from other supplier, when run the on hand report to see in stock of shampoo, how can get one balance instead showing 2 from one company and 3 from the other, what i want is to show balance in this case would be 5.

another word how can i create a record of same product from different suppliers.
when ship out it update balance of that product regardless of supplier.
thank you

 

[xenou]

In general, your inventory should be maintained by "your" part number, regardless of where it is purchased from. So you would just query for the inventory of the part. It is only on the purchasing side that they need to worry about which vendor, which means that on the purchasing side they make use of a table of suppliers of the part.