Cumulative stock by Id

[Xur]

Hello, i need help to make a query with the follow information, i attach a sample maybe can help

Order table
OrderDate----IdOrder--IdProduct--QuantityOrder
01-08-2013---10001---1501------100
01-08-2013---10001---1502------100
01-08-2013---10001---1503------100
01-08-2013---10001---1504------100
02-08-2013---10002---1501------200
02-08-2013---10002---1502------200
02-08-2013---10002---1503------200
02-08-2013---10002---1504------200

Stock table
IdProduct--QuantityStock
1501-------150
1502-------150
1503-------300
1504-------200


RESULTS!!!
OrderDate----IdOrder--IdProduct--QuantityOrder--StocktoOrder--NewStock
01-08-2013---10001---1501------100--------------100------------50
01-08-2013---10001---1502------100--------------100------------50
01-08-2013---10001---1503------100--------------100------------200
01-08-2013---10001---1504------100--------------100------------100
02-08-2013---10002---1501------200--------------50-------------(-150)


02-08-2013---10002---1502------200--------------50-------------(-150)
02-08-2013---10002---1503------200--------------200------------0
02-08-2013---10002---1504------200--------------100------------(-100)

TestDB.accdb

[ssanfu]

RESULTS!!!
OrderDate----IdOrder--IdProduct--QuantityOrder--StocktoOrder--NewStock
01-08-2013----10001-------1501---------100-----------------100----------------50

All of the data in blue are from the table "Orders".

You will have to explain about the last two columns:"StocktoOrder" and "NewStock". Where do the numbers in red come from??

[Xur]

All of the data in blue are from the table "Orders".

You will have to explain about the last two columns:"StocktoOrder" and "NewStock". Where do the numbers in red come from??

Hi, the "StocktoOrder" is the free stock allocation based on "QuantityOrder", and "NewStock" is the result of "QuantityStock" - "QuantityOrder"

[ssanfu]

the "StocktoOrder" is the free stock allocation based on "QuantityOrder"

Okay....... "based on" means ...what???? Is this a calculation?
What is the process?? Is this an inventory system?

Looked at your example dB. There are not any "idProducts" that match in the two tables, the numbers are different than what you posted.......... I'm confused!!


Words are free. No limit on this forum.
Remember, you understand what you want and how the dB should work; we do not know anything about your project. And my crystal ball is in the shop...

[Xur]

Okay....... "based on" means ...what???? Is this a calculation?
What is the process?? Is this an inventory system?

Looked at your example dB. There are not any "idProducts" that match in the two tables, the numbers are different than what you posted.......... I'm confused!!


Words are free. No limit on this forum.
Remember, you understand what you want and how the dB should work; we do not know anything about your project. And my crystal ball is in the shop...

first!, sorry, english isn't my language, i gonna try to explain me better

This is not a inventory system is an order system (an idea btw), i need to know how many orders are 100% covered with my stock from "Stock table", that gonna tell about how many orders can i dispatch and how many orders need to complete with new productions

example if the order calls for 100 units of product 1501, if exist in stock, such stock is then assigned, then that stock is no longer a free stock, changes to StocktoOrder

the NewStock is the result of the subtraction between StocktoOrder and Stock from Stock table

you tell me about de idproduct are not the same between two tables, this confused me because i see there existe the same code in both tables "1501, 1502, 1503..."

I really apologize if I do not understand well

[ssanfu]

you tell me about de idproduct are not the same between two tables, this confused me because i see there existe the same code in both tables "1501, 1502, 1503..."

When I down load TestDB.accdb from Poat#1:
In table "Stock", field "IdProduct" has 100001,100002, 100003, & 100004
In table "Order", field "IdProduct" has 1501, 1502, 1503 & 1504

This is not a inventory system is an order system (an idea btw)

OK, maybe you want an order system, but when you start trying to determine if the stock on hand covers the orders, you have an inventory system.

i need to know how many orders are 100% covered with my stock from "Stock table", that gonna tell about how many orders can i dispatch and how many orders need to complete with new productions

example if the order calls for 100 units of product 1501, if exist in stock, such stock is then assigned, then that stock is no longer a free stock

Yes, an inventory system....


Have you used pencil and paper first to draw out your requirements, describe what you want to do, table structures, relationships???
Most people start by creating tables, without fully understanding their requirements.

I usually have at least 3 or 4 revisions of tables and design before I start creating objects. And sometimes I still have to make changes and start over.