Calculation with query or report

[lico]

Hi everybody,
i'm buliding a database to manage a small business stock,
i have three tables:

one which contain the PRODUCTS INFORMATION and includes two fields product ID field and product name field.

the second table contains information about PRODUCTS IN STOCK, and feeds according to supply i get from suppliers, this table includes product ID field which is related to the PRODUCTS INFORMATION table, product name field and quantity field of each product i got from the supplier.

the third table includes information of OUT OF STOCK PRODUCTS and includes product ID field which is also related to the PRODUCTS INFORMATION table, product name field and quantity field of the product that was sold out.

the PRODUCTS IN STOCK table holds data of each product in a seprate record since the supply of every product is different in quantity. the OUT OF STOCK PRODUCTS table holds multiply values in the product field sinace the product are sold out in a packges and includes more then one product, and the quantity field referring to all the products in this field (each product*quantity).

i'm trying to calculate how many products do i have in stock, i tried to create two queries one that sums up the in stock and one that sums up the out of stock and create a query that substructs one from the other so i can see how many products remain in stock, it didn't work out, i get incorrect values.

my question is what will be the best way to do it, by query or a report? and how can i built it so i can see the products name in one field, the in stock of this product in the second field, the out of stock in the third field and forth field that calcultes how many of each product remain in stock.

thanksAlot

 

[HiTechCoach]

I would recommend this:

Inventory Control: Quantity on Hand

 

[lico]

Hi,

Thanks for your replay, the article describes exactly what i need, the problem is that i don't know how to work with sql code. is there a simpler way of doing it throw access query design view?

thanks again

 

[HiTechCoach]

Hi,

Thanks for your replay, the article describes exactly what i need, the problem is that i don't know how to work with sql code. is there a simpler way of doing it throw access query design view?

thanks again

Unfortunately, No. Inventory control is just very difficult to handle. IMHO, it is one of the most difficult tasks to program in any language, not just Access.

I think I have an example based on the article in the link I provided. I will see if I can find it and post it for you.

 

[lico]

thanks again for your help,
i managed to do it with the query grid. i still have small problem but i'll get over it.
thanks

 

[HiTechCoach]

You're welcome! Glad we could assist.

Good luck with your project ...