problem with an orders send to products

[Mehvan]

Hello Everyone



I created an OrderInvoics and ReceivedInvouce for company , because When We have Order to any products , We should to know how many type of Items we have at products

I created 1 Query for both Items ( Product_name , Items , OrderTons and OrderPrice , ReceivedTons , ReceivedPrice = OrderTons-ReceivedTons) here will tell me How many Tons stayed at products or you have on away .

but I have a problem with Price

for example : I bought this seize ( Pipe 40*40*1.50 MM = 50 tons by 500$ ) in 1/1/2017 , and after that I also bought that seize (30 tons by 510$) in 5/1/2017 , when that product send to me Email and buy that price it's normal , but when this seize comes to me by ( 515$ ) that 30 tons will be duplicate in program

it means will tell me , you have 100 tons in this products , in real I have 80 tons




I used Join between both ( Items to Items and Product_name to Product_name ) here I used Join for Product_name , duplicated my Items that seize to many Items by that name .

Can someone help me , please ? When send to me different price , it tell me , you entered different price or doesn't duplicate tons ?

[ranman256]

I would hope you have a tInventory table,that went from Qty 50 to 80.
and not use a tReceived table with 2 records ,one 50, one 30.

[Mehvan]

this program someone created by Microsoft Access 2003 , I think 7 years ago
but in that program doesn't have an Order

manager asked me to add an Order , and My Microsoft Access is 2010 , I was afraid to change many thing in that program , because still now , I don't have many information about programming

ًWhen we have an Order to products , We will have many types of Items , and also they will send to us contract and will say your Order will be ready after one month or more , but they will accept and will record our order and they will send to us contract with those Items with tons and price

because of that , I created ( head table and sub table ) for Order because one car bring many types of Items

you mean I should create 1 table for inventory and all tblInvoices I should connect with tblInventory ?