I am doing a stock management system.
In one table I store items:
ITEM<br> id_item<br> item_name<br> ORDER<br> id_order<br> date<br> ORDER_ITEM<br> id_ord_item<br> id_order : foreign key to ORDER id_order primary key<br> id_item : foreign key to ITEM table.<br> qty_ordered STOCK_IN : this table reflects item entries for related item quantities for orders<br> id_entry<br> id_ord_item: foreign key to ORDER_ITEM table.<br> description: optional<br> in_qty : In of a specific quantity for a specific item in a specific order<br> in_date: the date where the item quantity arrives to warehouse<br>
Having this structure: I wanted to show a report or query table like this containing the following fields:
item_name<br>, date (order date)<br>, description,<br> qty_ordered,<br> in_qty,<br> in_date.<br> qty_left (calculated field) qty_ordered-sum of in_qty to date<br>
Not sure if I should store the sum of arrive quantities to date in one column and use that to calculate the quantity left then. Next to those, I want to show a calculated field that in each row that is the pending qnantity left to arrive, which should be the aty_ordered - the sum of arrived item quantity to date (in_qty). Also, my goal was to show only the items that have a quantity left to arrive for a selected order. Items can come in different shippings, not all quantity ordered at once, and I want to register the date each shipping comes and the quantity of arrival of each item and the date.
I was thinking of not storing calculated values in table fields but I'm not sure if I should. Also I thought of showing all this in a single query, but I'm not sure if this is possible. Maybe I should make an agregate query for showing the order and it's item with the item quantity ordered, it's total quantity arrived to date and then in a separate query list all records of the ins of that item and their dates.
I can't figure out a way to do this propery. I thogght I could show a list of items grouped by items inside those orders, and then below list all entries.
I hope someone can though some light on this to me. I'm not sure how to approach this problem.