query for showing columns from various tables, grouping and showing calculated field in each row of the result

Question

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.

Answer 1

Basic is that you have all Tables set and also defined the relationship.

Then you add a new query.

Then you pull in all Table that you need, if your relationships are correctly set, all tables should ha a connection.

Now you pull all needed columns into the part below

and add a column that has the difference betwenn qty ordered and inqty

Now you have a corect query to run.

As SQL it looks like

SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered]
, STOCK_IN.in_qty, STOCK_IN.in_date, [qty_ordered]-[in_qty] AS qty_left
FROM ([ORDER] 
      INNER JOIN (ITEM 
       INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item) ON ORDER.id_order = ORDER_ITEM.id_order) 
       INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

For your query to have the complete quantity for all orders

SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered], STOCK_IN.in_qty, STOCK_IN.in_date, [Sum1] AS qty_left
FROM 
    ([ORDER] INNER JOIN (
                                           ( ITEM INNER JOIN (SELECT ORDER_ITEM.id_item
                                                                       ,SUM( [qty_ordered]-[in_qty]) AS Sum1
                                                                       FROM ([ORDER] INNER JOIN ORDER_ITEM ON ORDER.id_order = ORDER_ITEM.id_order) INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item
                                                                       GROUP BY ORDER_ITEM.id_item) qtysum ON qtysum.id_item = ITEM.id_item
                                           )
INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item
) ON ORDER.id_order = ORDER_ITEM.id_order) 
INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

Result: