0

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.

0

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

enter image description here

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: enter image description here

3
  • The thing is, the STOCK_IN table can have multple records for a single ORDER_ITEM id. Imagine for Item1 for order1 I have an ordered quantity of 10. First Stock in record, has an InQty of 3, which leads to 7 left. The next record has 4 InQty, and if ou write the formula like that it will say I have 6 left, when in reality I want to say that I have 7 from the previous formula - the 4 of the new record which will leave me with 3. I pretty much don't know if this is possible in Access 2003 SQL or I need to do separate queries for this, since I can't figure out to select previous record field. – Mister Equis Mar 14 at 22:01
  • So you want a SUM(qty_left) for all items in the qty_left column ? you have to add a group by item and sum of qty_left and add it to the mix – nbk Mar 14 at 22:17
  • i added another query so that you can see how you can acomplish that – nbk Mar 14 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.